Skip to main content

Date and Time Filter Aggregation Functions

  • Updated

Date and Time Filter Aggregation Functions

To filter a custom metric using dates or times, you must already have a time attribute configured in your data source. The following date and time functions can only be used after WHERE in your custom metric.

Date field options use common time formats such as YTD, MMDDYYYY, and YoY.

The following date and time filter aggregation functions are supported.

Supported Date and Time Functions
FunctionDescription
DATE()Deprecated. Use NOW() instead.

DateADD('<time_period>',<interval>,'<date>')

Deprecated. Use TIME_ADD instead.

For example, consider this DateADD specification:

DateADD('YEAR', 1, '2021-01-01')

Use this TIME_ADD specification instead:

TIME_ADD('YEAR', 1, '2021-01-01')

In a second example, consider this DateADD specification:

DateADD('MONTH', 1, DATE())

Use this TIME_ADD specification instead:

TIME_ADD('YEAR', 1, NOW())

DateSUB('<time_period>',<interval>,'<date>')

Deprecated. Use TIME_ADD instead, specifying a negative number for interval.

For example, consider this DateADD specification:

DateSUB('YEAR', 1, '2021-01-01')

Use this TIME_ADD specification instead:

TIME_ADD('YEAR', -1, '2021-01-01'

TIME_ADD supports negative interval numbers for subtraction.

NOW()

Obtains the current date and time for the derived field. However, NOW() functionality is not enabled by default. To enable this functionality, you must set the calculations.rle.now.function property in the query-engine.properties file to true and restart the query engine microservice. See Query Engine Properties.

PreviousPeriod(<offset>,<numPeriods>)

This function is supported only within a TRANSFORM clause used for filtering the custom metric.

The period returned is of the same length as the currently represented period, but not immediately prior to it. Instead, it counts back in <numPeriods> periods of time, measured in units named by <offset>.

The following time <offset> values are supported: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND.

See PreviousPeriod Function.

TIME()Deprecated. Use NOW() instead.
TIME_ADD('<time_period>',<interval>, <date-time-field>)

Adds an interval value to the <timepart> of the date-time field:

In the following example, 7 is added to the hour in the field called date_time_field:

TIME_ADD ('HOUR', +7, date_time_field)

Date Filter Functions

Specific parameters are needed for the DateADD and DateSub functions. The following table describes them.

Parameter Value
time_period

Supported time periods (with corresponding interval range): YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND

interval

Whole number integer. Negative numbers are supported for subtraction.

date

  • Current day operator: date()

  • Standard date and time formats supported by Composer, including:

    • yyyy-MM-dd HH:mm:ss

    • MM/dd/yy hh:mm aa

    • yyyy

    For all supported formats, see Supported Date and Time Formats.

PreviousPeriod Function

The PreviousPeriod function is used for comparing data values between different time periods. This function can be used when you need to compare one time period to another of equivalent size for variance custom metrics. For example, comparing results from the current month to the previous month or the current week to the same week one year ago.

Note that this function only works when the date field used in the formula is selected on the time bar.

To use this function, the TRANSFORMSQL-like expression must be used in the custom metric to convert the date range for a specified time attribute. For example: 

SUM(Sales) TRANSFORM saledate = PreviousPeriod('month',1)

If the saledate time period is March 2015, the custom metric returns SUM(Sales) where the saledate is February 2015.​

If the data is grouped by the same field for which a PreviousPeriod transformation is performed and it is grouped by days but transformed by units of months, quarters, or years, null values are returned when the previous period does not have matching days for the current period. For example, if the current period is the month of March and PreviousPeriod('month',1) is used for the transformation, null values are produced for February 29-31, 2015 because those days are not valid days (although they are valid days for March 2015). Composer attempts to preserve the day-of-month correspondence between the two periods.

Specific parameters must be specified in PreviousPeriod functions. The following table describes them.

Parameter Value
offsetThe time granularity for the previous period (includes YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND).
numPeriodsThe argument specifying the number of periods to go back in time.

Related articles

Comments

0 comments

Please sign in to leave a comment.