Example filter: Last 12 months

This topic provides an expression for a date filter that includes the last 12 months relative to the _as_of_date parameter.

Paste this expression in the filter editor to create the Last 12 months date filter.

// validate: 1 = 1
#$_this.parent.idForExpression# >=
     #queryValue($_this.parent.split.ref + '.dMonth',
                 $_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 + '.dMonth',
                 $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)#
   

Here are the steps that were used to build this expression:

  1. Identify the calendar columns to use.

    The filter uses the calendar columns TheDate, dMonth, and PY_TheDate from the sample Gregorian calendar data module.

    For more information, see Sample calendars.

  2. Define the filter lower and upper bounds.

    The filter lower bound is the first day of the month that is 12 months prior to the month containing the date that is represented by the _as_of_date parameter. The filter upper bound is the last day of the last complete month, relative to the date that is represented by the _as_of_date parameter.

    The following table shows the move intervals for the lower bound date when the _as_of_date date (TheDate) is January 19, 2019.

    TheDate PY_TheDate dMonth
    2019-01-19 2018-01-19  
    2018-01-19   2018-01-01

    The filter lower bound date is 2018-01-01. The filter upper bound date is 2018-12-31.

  3. Define the move intervals for the lower bound and upper bound timelines.

    The timeline consists of move intervals that are based on the columns PY_TheDate and dMonth .

    The following move intervals exist for the lower bound timeline:
    • Move interval 1: PY_TheDate
    • Move interval 2: dMonth

    The move interval for the upper bound timeline is dMonth.

    Here is a graphical representation of the move intervals for the lower bound timeline when January 19, 2019 is the _as_of_date date.

    Graphical representation of a move interval
  4. Map each move interval to one queryValue macro.

    The expression of the lower bound consists of two queryValue macros. Each queryValue maps to one move interval within the lower bound expression. The initial move interval (PY_TheDate ) is nested within the second move interval (dMonth), as shown below:

    
    #$_this.parent.idForExpression# >=
            #queryValue($_this.parent.split.ref + '.dMonth',
                        $_this.parent.split.ref + '.TheDate = ' + 
                          queryValue($_this.parent.split.ref + '.PY_theDate' ,
                                     $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)
            )#
       

    The expression of the upper bound consists of one queryValue macro, as shown below:

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

    This expression uses the less than (<) sign because the filter includes only dates prior to the upper bound, and not equal to the upper bound itself.