Expression variables

The relative date filter expression uses a set of variables to define the filter conditions. The variables evaluate to specific values when the filter is applied in visualizations.

Use the information in this topic when creating relative date filter expressions.

The following Prior Year filter expression from the sample Gregorian calendar can be used as an illustration when reading descriptions of the variables:

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

$_this.parent

This variable refers to a table column of type Date whose Lookup reference property is set to the calendar that you are referencing. The table with the Date column is in the data module that is used for relative date analysis. For more information, see Creating a data module for relative date analysis.

For example, in the following data module, $_this.parent refers to the Open Date column.

Example of data module tree

The following two variables function within the context of the Date column:

  • $_this.parent.idForExpression

    This variable evaluates to the idForExpression for the Date column. The idForExpression is the full identifier that uniquely identifies the Date column within the data module. This identifier is not viewable from the user interface.

  • $_this.parent.split.ref

    This variable evaluates to the calendar that is referenced by the Lookup reference property of the Date column.

All date filters in the calendar, including the new ones that you add, are accessible as child filters of the Date column in the data module that references this calendar through the Lookup reference property. The filters are used for relative date analysis in reports and dashboards.

queryValue

queryValue is one of the macro functions that Cognos® Analytics provides.

Tip: To view the description of the queryValue macro, click the functions tab in the expression editor and search for the macro. The description is shown in the Information pane.

Within the context of relative date filters, queryValue returns the date value from the specified Date column, at the specified date. The specified Date column is the first parameter to the queryValue function. The second parameter is the specified date.

In the following example from the year-to-date (YTD) filter, the queryValue returns, from the calendar column dYear, the date where the calendar TheDate date equals to the _as_of_date date.

#queryValue($_this.parent.split.ref + ‘.dYear’,
                    $_this.parent.split.ref + ‘.TheDate = ‘ + $_as_of_date)#