Creating relative date filters

A relative date filter specifies a range of dates that are relative to the _as_of_date global parameter.

The sample Gregorian and Fiscal calendars already contain a number of predefined relative date filters. If you need custom filters, you can add them to these calendars.

Before you begin

  1. Know the columns in the sample calendar.

    New filters are added to the sample Gregorian or Fiscal calendar data modules. To understand how these calendars are structured, look at the columns, and view dates in different columns in the same row. For example, in the Gregorian calendar data module, for the column TheDate with the value of September 30, 2018, the related values for columns dYear, PY_TheDate, and dMonth are shown in the following table:

    TheDate dYear PY_TheDate dMonth
    2018-09-30 2018-01-01 2017-09-30 2018-09-01

    For more information, see Sample calendars.

  2. Conceptualize the filter lower bound and upper bound in relation to the _as_of_date global parameter.

    A relative date filter defines a range of dates between the filter lower bound (range start) and upper bound (range end) dates. The lower and upper bounds are set against a reference date that is the _as_of_date parameter value.

    For example, for a year-to-date (YTD) filter, the lower bound date is the first day of the first month in the year that contains the _as_of_date date. The upper bound date is the date that is the _as_of_date parameter value. If the _as_of_date parameter is December 19, 2018 (TheDate), the lower bound date is January 1, 2018, and the upper bound date is December 19, 2018.

    By default, the _as_of_date parameter has a value of today. However, it can be set to a different date. For more information, see Customizing the reference date

  3. Build the filter expression.

    The critical element of creating a relative date filter is the filter expression. Familiarize yourself with the expression syntax and variables before you start entering the code in the expression editor. For more information, see Creating filter expressions.

Procedure

  1. From the Team content > Calendars folder, open the sample calendar data module where you plan to add the new filter.

    The data module contains one table with a number of existing filters. Add your new filter to this table.

  2. From the table context menu, click Filter.
  3. In the filter editor that is displayed, type the new filter name.
  4. In the Expression pane, type or paste the filter expression.

    For example, to create the Last 12 months filter, enter the following expression:

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

    Another filter example that you can use is Next 4 months.

    For more information, see Creating filter expressions.

  5. Validate the expression.
    Validation of date filter expressions must be done manually because the validate button Validation icon in expression editor in the expression editor doesn't validate macro expressions. So you can only visually confirm that the following elements are correct:
    • The expression is preceded with the // validate: 1 = 1 comment.
    • The outer block of the queryValue macro function is enclosed within hash marks (#).
    • Each queryValue has matching round brackets () for its two arguments.
    Tip: You can debug filter expressions in Reporting. To do so, open a report that contains the relative date filters, and set the validation option in the report to Information.
  6. Click OK.
    The new filter is added to the calendar table at the top of the list of filters. You can drag the filter to change its position in the list. The filter is created even if the expression contains errors. To modify the filter, from its context menu, click Edit filter.

Results

The new filter is now available to the data modules that reference this calendar through the Lookup reference property, and can be used for relative date analysis.
Tip: The new filter, as other filters in the calendar, should remain hidden.