Date Range Filter Interactions

Use the asDateRange() serialization function to bind filters based on date ranges. You can create filters using absolute or relative date ranges.

If the input data is a one-dimensional array with two elements:

And both elements are numbers, CRM Analytics assumes the numbers are epoch times. [1016504910000, 1016504910000] results in fieldName in [dateRange([2002,3,19], [2010,8,12])].
Otherwise, the first element is used as the minimum and the second element is used as the maximum. ["current day", "1 month ahead"] results in fieldName in ["current day".."1 month ahead"]. If one of the elements is null, the date range is open-ended. ["1 month ago", null] results in fieldName in ["1 month ago"..].

If the input data is a two-dimensional array where the outer array has two elements:

And both nested arrays have two elements, CRM Analytics assumes the data is in the relative date array format. [["year", -2], ["year", 1]] results infieldName in ["2 years ago".."1 year ahead"].
And both nested arrays have 3 elements, the nested arrays are passed to the SAQL dateRange() function. [[2015, 2, 1], [2016, 2, 1]] results in fieldName in [dateRange([2015,2,1], [2016,2,1])].

If the input data is null, the result is fieldName in all, which doesn’t filter anything.

Binding to a Date Filter Widget

For instance, let’s say you make a selection in a date widget that returns this absolute date range (in epoch format).

1[ {min: 1016504910000, max: 1281655993000} ]

You can create a filter using the returned selection data.

1q = filter q by {{row(queryFoo.selection, [0], ["min", "max"]).asDateRange("date(year, month, day)")}};

After evaluating the binding, CRM Analytics produces this date range filter.

1q = filter q by date(year, month, day) in [dateRange([2002,3,19], [2010,8,12])];

For relative dates, assume that the date widget returns these relative datas based on your selection.

1[ {min: ["quarter", -2], max: ["quarter", 3]} ]

After evaluation, this data range filters results.

1q = filter q by date(year, month, day) in ["2 quarters ago".."3 quarters ahead"];

Binding to a Custom List of Date Ranges

Create a custom query with rows for each custom date range. You can specify ranges using absolute or relative dates.

To filter with absolute ranges, the results of the custom query must return absolute dates.

1[
2    {label: "8/30/15 - 8/30/16", range: [[2015, 8, 30], [2016, 8, 30]]}
3    {label: "7/30/16 - 8/30/16", range: [[2016, 7, 30], [2016, 8, 30]]}
4]

You can create the filter based on the selected value of the source query.

1q = filter q by {{cell(queryFoo.selection, 0, "range").asDateRange("date(year, month, day)")}};

After CRM Analytics evaluates the binding, the filter is as shown.

1q = filter q by date(year, month, day) in [dateRange([2015, 8, 30], [2016, 8, 30])];

To filter with relative ranges, the source query results must be as shown.

1[
2    {"label": "YTD", "range": ["1 year ago", "current day"]}
3    {"label": "MTD", "range": ["1 month ago", "current day"]}
4    {"label": "Everything up to today", "range": [null, "current day"]}
5]

You can use this binding to create a filter based on the selected value of the source query.

1q = filter q by {{cell(queryFoo.selection, 0, "range").asDateRange("date(year, month, day)")}};

After CRM Analytics evaluates the binding, the filter becomes:

1q = filter q by date(year, month, day) in ["1 year ago".."current day"];

You can also create an open-ended range filter by specifying null as one of the relative date keywords in the source query. The bound filter is as shown.

1q = filter q by {{cell(queryFoo.selection, 2, "range").asDateRange("date(year, month, day)")}};

After CRM Analytics evaluates the binding, the filter becomes:

1q = filter q by date(year, month, day) in [.."current day"];

The SAQL function date_to_epoch() returns epoch seconds, but date range filters bindings require milliseconds.

Note