Creating filter expressions

A relative date filter is based on an expression. The expression defines the filter lower and upper bounds, and the timeline between the bounds. The timeline is mapped to the queryValue macro.

When you create a new filter, you enter the expression in the expression editor.

Use the following syntax to create the filter expression:

// validate: 1 = 1
#$_this.parent.idForExpression# >= lower_bound_date expression#
AND
#$_this.parent.idForExpression# <= upper_bound_date expression#

For example, the year-to-date (YTD) filter that is available in the sample calendars data modules uses the following expression:

A year-to-date (YTD) filter expression

In this filter, expression 1 is the filter lower_bound_date expression. Expression 2 is the filter upper_bound_date expression.

The lower bound and upper bound code blocks are combined by using the AND operator.

Tip: The comment // validate: 1 = 1 must always be included at the beginning of the expression.

The lower_bound_date expression and upper_bound_date expression are the elements that you must define for your filter. The remaining part of the expression remains unchanged for all filters.

For a description of variables that are used in relative date filter expressions, see Expression variables.

To define the lower bound and upper bound expressions, you need to complete the following tasks:

Identify the move intervals for the filter timeline

A timeline starts from the _as_of_date date, and then uses one or more move intervals (units of time) to reach the lower bound or upper bound date.

The sample calendars support the following move intervals: day, month, quarter, and year.

A move interval is expressed by using the following calendar columns:

  • PD_TheDate - move to the previous day
  • ND_TheDate - move to the next day
  • dYear - move back to the first day of the year
  • PY_TheDate- move back to the same or equivalent date in the previous year
  • NY_TheDate - move forward to the same or equivalent date in the next year
  • dQuarter - move back to the first day of the quarter
  • PQ_TheDate - move back to the same or equivalent date in the previous quarter
  • NQ_TheDate - move forward to the same or equivalent date in the next quarter
  • dMonth - move back to the first day of the month
  • PM_TheDate - move back to the same or equivalent date in the previous month
  • NM_TheDate - move forward to the same or equivalent date in the next month

The type of filter implies which columns are used to express the timeline. For example, in the year-to-date (YTD) filter, the dYear column is the lower bound move interval, as shown in the following graphic. There is no upper bound move interval for this filter.

Year-to-date (YTD) filter

Map the move intervals to queryValue macros

After you identify the move intervals for the filter lower and upper bounds, you need to map each move interval to one queryValue macro.

The queryValue uses the following syntax.

#queryValue($_this.parent.split.ref + move_interval ,
$_this.parent.split.ref + ‘.TheDate =‘ + date)#

For example, here is how the move interval dYear is mapped to the queryValue macro (parts of the code in bold font) in the year-to-date (YTD) filter expression: 

// validate: 1 = 1
#$_this.parent.idForExpression# >=
        #queryValue($_this.parent.split.ref + '.dYear',
                    $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)#
AND
#$_this.parent.idForExpression# <= #$_as_of_date#

Depending on the type of date filter, your expression might include multiple, nested queryValue macros, such as in the following Prior YTD filter:

// validate: 1 = 1
#$_this.parent.idForExpression# >=
  #queryValue($_this.parent.split.ref + '.dYear',
              $_this.parent.split.ref + '.TheDate = ' +
              queryValue($_this.parent.split.ref + '.PY_TheDate',
                         $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)
  )#

AND
#$_this.parent.idForExpression# <=
   #queryValue($_this.parent.split.ref + '.PY_TheDate',
               $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)#

You can find another example of nested queryValue macros in the Next 4 months filter.