Other examples of relative date filters

This topic provides examples of relative date filter expressions.

Copy and paste the selected expression in the filter editor when creating the filter.

Note: To pass validation, the line // validate: 1 = 1 must remain in the expression as a comment.

The following examples are available:

Last 12 complete months
Prior month last year
Prior YTD 2 years ago
Prior year yesterday
Prior year 2 years ago
Yesterday
Last 7 days

Last 12 complete months


// 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)#

Prior month last year

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

Prior YTD 2 years ago

// 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 = ' +
                                  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 = ' + 
                    queryValue($_this.parent.split.ref + '.PY_TheDate',
                               $_this.parent.split.ref + '.TheDate = ' + $_as_of_date) 
   )#

Prior year yesterday

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

Prior year 2 years ago

// 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 = ' +
                                  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 = ' + 
                    queryValue($_this.parent.split.ref + '.dYear',
                               $_this.parent.split.ref + '.TheDate = ' + $_as_of_date) 
   )#

Yesterday

// validate: 1 = 1
#$_this.parent.idForExpression# = _add_days ( #$_as_of_date#, -1 )

Last 7 days

// validate: 1 = 1
#$_this.parent.idForExpression# > #_add_days ($_as_of_date, -7)#
AND 
#$_this.parent.idForExpression# <= #$_as_of_date#