Working with Dates in Salesforce Wave – Part 1

Date component is a powerful feature in Salesforce Wave. Let’s walk through how Dates are used from loading the dataset, filtering and displaying in Dashboards.

Loading the Dataset

Date Format

Important consideration while loading the dataset is the date format. Make sure we have the correct data format set before loading the data.

Default Values

In the Metadata JSON, we can set default values.

“fields” : [ {

“name” : “Date”,

“fullyQualifiedName” : “Date”,

“label” : “Date”,

“description” : null,

“type” : “Date”,

“precision” : 0,

“scale” : 0,

“decimalSeparator” : “.”,

“defaultValue” : “01/01/1960”,

“format” : “MM/dd/yyyy”,

“isSystemField” : false,

“isUniqueId” : false,

“isMultiValue” : false,

“multiValueSeparator” : null,

“fiscalMonthOffset” : 0,

“firstDayOfWeek” : -1,

“isYearEndFiscalYear” : true,

“isSkipped” : false,

“sortIndex” : 0,

“isSortAscending” : true,

“isComputedField” : false,

“computedFieldExpression” : null

},

Fiscal Year Calculation

“fields” : [ {

“name” : “Date”,

“fullyQualifiedName” : “Date”,

“label” : “Date”,

“description” : null,

“type” : “Date”,

“precision” : 0,

“scale” : 0,

“decimalSeparator” : “.”,

“defaultValue” : “01/01/1960”,

“format” : “MM/dd/yyyy”,

“isSystemField” : false,

“isUniqueId” : false,

“isMultiValue” : false,

“multiValueSeparator” : null,

“fiscalMonthOffset” : 0, // This field needs to be set for fiscal year calculations.

“firstDayOfWeek” : -1,

“isYearEndFiscalYear” : true,

“isSkipped” : false,

“sortIndex” : 0,

“isSortAscending” : true,

“isComputedField” : false,

“computedFieldExpression” : null

},

Filters

Compact Filters

Compact filters come handy to setup relative filtering. e.g Current Month.

“date_filter_current”: [

[  [“month”, 0],[ “month”,0] ]]

Same code can be modified to support Month to Date or Year to Date.

“date_filter_current”: [[[“month”,0],[“day”,0]]] 

SAQL filters

Similarly SAQL code can be used to filter Year to Date

Sample below

s = load \”sample_data\”;

s = filter s by date(‘Date_Year’,’Date_Month’,’Date_Day’) in [\”current year\”..\”current day\”]; 

Selection Binding

Most of the time, we need to apply compact filters for certain steps and SAQL filter for few. It’s a good practice to set up selection binding to include both compact and SAQL filters.

Static Step

“CustomDateFilter”: {

“selectMode”: “single_required”,

“values”: [

{

“display”: “This Month”,

“value”: [

{

“date_filter_current”: [[[“month”,0],[“month”,0] ]],

“date_filter_prev”: [[[“month”,-12],[“month”,-12]]],

“date_filter_prev_saql”: “\”12 months ago\”..\”12 months ago\””,

“date_filter_current_saql”: “\”current month\”..\”current month\””

}]},

Using the compact filter in a step

“filters”: [

[

“Converted_Date”,

    “{{ value(field(selection(CustomDateFilter),’date_filter_current’)) }}”

]

]

},

Using the SAQL filter in a step

q = load \”sample_data\”;

q = filter q by date(‘Date_Year’, ‘Date_Month’, ‘Date_Day’) in [{{no_quote(value(field(selection(CustomDateFilter),’date_filter_current_saql’))) }}]; 

In the next blog we will discuss about fiscal date, date groups and other filter options.