Share via

2.6.162 CT_DynamicRichFilter

  • Article

Target namespace: http://schemas.microsoft.com/office/spreadsheetml/2017/richdata2

Referenced by: CT_RichFilterColumn

This complex type specifies the dynamic filter criteria associated with rich values. These criteria are considered dynamic because they can change, either with the data itself (e.g., "above average") or with the current system date (e.g., show values for "today"). For any cells within the filter range whose values do not meet the specified criteria, the corresponding rows will be hidden from view when the filter is applied. This complex type extends the base type CT_DynamicFilter ([ISO/IEC29500-1:2016] section 18.3.2.5)

Attributes:

key: An ST_RichFilterKey (section 2.7.38) attribute that specifies the rich value key for which the filters is defined.

type: An ST_DynamicFilterType ([ISO/IEC29500-1:2016] section 18.18.26) attribute that specifies the type of dynamic filter.

val: A double ([XMLSCHEMA2/2] section 3.2.5) attribute that specifies a minimum value for dynamic filter. It is inclusive for today, yesterday, tomorrow, nextWeek, thisWeek, lastWeek, nextMonth, thisMonth, lastMonth, nextQuarter, thisQuarter, lastQuarter, nextYear, thisYear, lastYear, and yearToDate. The val attribute is not required for Q1, Q2, Q3, Q4, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11 and M12. It is exclusive for aboveAverage and belowAverage.

maxVal: A double ([XMLSCHEMA2/2] section 3.2.5) attribute that specifies a maximum value for dynamic filter. It is exclusive and required for today, yesterday, tomorrow, nextWeek, thisWeek, lastWeek, nextMonth, thisMonth, lastMonth, nextQuarter, thisQuarter, lastQuarter, nextYear, thisYear, lastYear, and yearToDate.

valIso: A dateTime ([XMLSCHEMA2/2] section 3.2.7) attribute that specifies a minimum value for dynamic filter. (See description of the maxValIso attribute to understand when the valIso attribute is required.)

maxValIso: A dateTime ([XMLSCHEMA2/2] section 3.2.7) attribute that specifies a maximum value for dynamic filter. The maxValIso attribute is required for today, yesterday, tomorrow, nextWeek, thisWeek, lastWeek, nextMonth, thisMonth, lastMonth, nextQuarter, thisQuarter, lastQuarter, nextYear, thisYear, lastYear, and yearToDate.

The above criteria are based on a value range; that is, if today's date is September 22nd, then the range for thisWeek is the values greater than or equal to September 17 and less than September 24. In the thisWeek range, the lower value is expressed valIso. The higher value is expressed using the maxValIso attribute.

These dynamic filters do not require the valIso or maxValIso attributes:

Q1, Q2, Q3, Q4, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11 and M12.

The above criteria shall not specify the range using valIso and maxValIso because Q1 starts from M1 to M3, and M1 is January.

These types of dynamic filters use valIso and do not use maxValIso:

aboveAverage and belowAverage

The following W3C XML Schema ([XMLSCHEMA1/2] section 2.1) fragment specifies the contents of this complex type.

 <xsd:complexType name="CT_DynamicRichFilter">
   <xsd:complexContent>
     <xsd:extension base="x:CT_DynamicFilter">
       <xsd:attribute name="key" type="ST_RichFilterKey" use="optional"/>
     </xsd:extension>
   </xsd:complexContent>
 </xsd:complexType>

See section 5.20 for the full W3C XML Schema ([XMLSCHEMA1/2] section 2.1).