Zoomdata Version

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 calculation.

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
Function Type Description
DateADD(<time_period>,<interval>,<date>)
date formats

Increments the specified date to return a new date. The increment is defined by the <time_period> and <interval>. See Date Filter Functions.

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

DateSUB(time_period,interval,date)
date formats

Decrements the specified date to return a new date. The decrement is defined by the <time_period> and <interval>. See Date Filter Functions.

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

PreviousPeriod(<offset>,<numPeriods>)
time granularity

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

Without parameters, the function returns the period immediately before the currently represented data and of the same length. For example, if you are currently viewing data for March, the function returns data from February.

With parameters, 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: years, quarters, months, weeks, days, hours

See PreviousPeriod Function.

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): YEARS, MONTHS, WEEKS, DAYS

interval Whole number integer
date
  • Current day operator: date()

  • Standard date and time formats supported by Zoomdata, 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 TRANSFORM SQL-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()

If the 'saledate' time period was set from Jan 1, 2015 through Dec 31, 2015, then the custom metric would return SUM(Sales) where 'saledate' is from Jan 1, 2014 through Dec 31, 2014.​

Specific parameters can be specified in PreviousPeriod functions. They are all optional. The following table describes them.

Parameter Value
offset Optional time granularity for the previous period (includes day, week, month and year).
numPeriods Optional argument specifying the number of periods to go back in time. Default should be 1.

Was this topic helpful?