Using date fields in static steps
Earlier I wrote a post about static steps and how they can give the dashboard viewer additional power in exploring data [read the post here]. I showed how to use static steps as a measure, grouping, filter, order and limit. However, all my examples used dimensions or of course measure, they did not use a date field. So I thought I would show how to use dates in a static step as a grouping and filter. My example shows the sum of amount based grouped by either Year-Month or Year-Month-Day, further it can be filtered by last, this or next month.
First, make sure to have a dashboard created with at least one graph. To create the static step to be used for a grouping go to your dashboard designer, click “Create Step” and in the bottom “Create a Static Step with Custom Values”. My example is using the field Close Date with a combination of first Year-Month and then Year-Month-Day.
For Year-Month use the value: [“CloseDate_Year”,”CloseDate_Month”]
For Year-Month-Day use the value: [“CloseDate_Year”,”CloseDate_Month”,”CloseDate_Day”]
Note that for my grouping I changed the step properties to use “single required” for selection options, that way I force a grouping through. Per default, it will select the first value unless you pick another initial filter.
For the date filter I am using “last month”, “this month” and “next month”. Follow the same steps to create a static step to be used for your filter and used in the following values.
For Last Month use the value: [ “Close Date”, [ [ [“month”,-1],[“month”,-1] ] ],”>=<=“ ]
For This Month use the value: [ “Close Date”, [ [ [“month”,0],[“month”,0] ] ],”>=<=“ ]
For Next Month use the value: [ “Close Date”, [ [ [“month”,1],[“month”,1] ] ],”>=<=“ ]
Make sure that your two new static steps are added to a toggle widget.
In the dashboard JSON the static steps should look like this:
"StaticFilter_1": {
"type": "staticflex",
"values": [
{
"display": "Last Month",
"value": [
"Close Date",
[
[
[
"month",
-1
],
[
"month",
-1
]
]
],
">=<="
]
},
{
"display": "This Month",
"value": [
"Close Date",
[
[
[
"month",
0
],
[
"month",
0
]
]
],
">=<="
]
},
{
"display": "Next Month",
"value": [
"Close Date",
[
[
[
"month",
1
],
[
"month",
1
]
]
],
">=<="
]
}
],
"isFacet": false,
"useGlobal": false,
"isGlobal": false,
"selectMode": "single",
"label": "StaticFilter"
},
"StaticGrouping_1": {
"datasets": [],
"dimensions": [],
"groups": [],
"isFacet": false,
"isGlobal": false,
"label": "StaticGrouping",
"numbers": [],
"selectMode": "singlerequired",
"strings": [],
"type": "staticflex",
"useGlobal": false,
"values": [
{
"display": "Close Year-Month",
"value": [
"CloseDate_Year",
"CloseDate_Month"
]
},
{
"display": "Close Year-Month-Day",
"value": [
"CloseDate_Year",
"CloseDate_Month",
"CloseDate_Day"
]
}
],
"start": {
"display": [
"Close Year-Month"
]
}
}
In order to make sure that the static steps apply to my graph, the dashboard JSON needs to be modified. Find the step and make sure to add the new grouping and filter. It should look like this:
"StepTotalAmountMonth_1": {
"datasets": [
{
"id": "0Fb58000000CeCnCAK",
"label": "Opportunity with Account and Campaign",
"name": "Opportunity_with_Account_and_Campaign",
"url": "/services/data/v39.0/wave/datasets/0Fb58000000CeCnCAK"
}
],
"isFacet": true,
"isGlobal": false,
"label": "StepTotalAmountMonth",
"query": {
"measures": [
[
"sum",
"Amount"
]
],
"groups": "{{column(StaticGrouping_1.selection, ["value"]).asObject()}}",
"filters": "{{column(StaticFilter_1.selection, ["value"]).asObject()}}"
},
"selectMode": "single",
"type": "aggregateflex",
"useGlobal": true,
"visualizationParameters": {
"visualizationType": "vbar",
"options": {}
}
},
This is it. Now your dashboard viewers should be able to use Close Date as a grouping and as a filter.
Correction 19. November 2017
If you are getting an error following the steps it may be that the behind JSON is being wrongly formatted. Check out the fix in this blog. Though it is for the grouping the concept can be applied to filters as well.
Correction 19. May 2018
In the Spring 18 release, Salesforce introduced a new parameter ‘columnMap’, which means the bindings described in this blog will throw an error unless the ‘columnMap’ is adjusted as described in this blog.
Written by
Hi Rikke,
When I am trying the same code, I recieve an error ” This widget can’t be displayed because there is a problem with its source step:
a.replace is not a function”
My code is:
“Static_Date_1”: {
“datasets”: [],
“dimensions”: [],
“groups”: [],
“isFacet”: false,
“isGlobal”: false,
“label”: “Static_Date”,
“numbers”: [],
“selectMode”: “singlerequired”,
“strings”: [],
“type”: “staticflex”,
“useGlobal”: false,
“values”: [
{
“display”: “Y”,
“Value”: “CloseDate_Year”
},
{
“display”: “Q”,
“Value”: [
“CloseDate_Year”,
“CloseDate_Quarter”
]
}
]
}
“query”: {
“measures”: [
[
“count”,
“*”
]
],
“groups”: [
“{{column(Static_Date_1.selection, [\”Value\”]).asObject()}}”,
“Stage.StageName”
],
“filters”: [
[
“Close Date”,
[
[
[
“year”,
0
],
[
“year”,
1
]
]
],
“>=<="
]
]
}
Can you help me please.
Kind regards
Stephane
Hi Stephane,
The logic looks alright. I would try including the stage grouping in the binding that might solve your issue.
Rikke
I have had a very similar experience except that I am trying to include Account Name along with a binding-driven date grouping. I know that the date binding works when used as a grouping by itself.
I tried putting the additional grouping both inside the binding itself and outside as Stephanie’s example here, but in both cases I received the error “e.replace is not a function.” I am starting to think this is an Einstein limitation or there is additional binding into the Column Map that needs to happen to support the Stacked Bar type.
Hey Rikke,
Have you been able to successfully use date fields in static steps for connected data sources? We have a use case where we’re accomplishing date grouping by Year-Quarter, Year-Month, and Year-Month-Day, but attempting to facet by a grouping will not apply to the connected data source. SF support has been of moderate help.
Thanks,
Moriah
Hi Moriah,
Not 100% if I understand the question. But in general, I have had issues with date bindings using the connect data sources. What I did is create my own using the split date field. So I might have Start_Date as a field, but instead I will bind Start_Date_Year with Date_Year, Start_Date_Month with Date_Month. Example:
“dataSourceLinks”: [
{
“fields”: [
{
“dataSourceName”: “trip”,
“dataSourceType”: “saql”,
“fieldName”: “start_date_Year”
},
{
“dataSourceName”: “weather”,
“dataSourceType”: “saql”,
“fieldName”: “date_Year”
},
{
“dataSourceName”: “station”,
“dataSourceType”: “saql”,
“fieldName”: “installation_date_Year”
}
],
“label”: “DateYear”,
“name”: “Link_1”
},
{
“fields”: [
{
“dataSourceName”: “trip”,
“dataSourceType”: “saql”,
“fieldName”: “start_date_Month”
},
{
“dataSourceName”: “weather”,
“dataSourceType”: “saql”,
“fieldName”: “date_Month”
},
{
“dataSourceName”: “station”,
“dataSourceType”: “saql”,
“fieldName”: “installation_date_Month”
}
],
“label”: “DateMonth”,
“name”: “Link_2”
},
If you are using the date selection as a combination of Year – Month then you can also try with the Start_Year~~~Start_Month version. An example would be:
{
“fields”: [
{
“dataSourceName”: “trip”,
“dataSourceType”: “saql”,
“fieldName”: “start_date_Year~~~start_date_Month”
},
{
“dataSourceName”: “weather”,
“dataSourceType”: “saql”,
“fieldName”: “date_Year~~~date_Month”
},
{
“dataSourceName”: “station”,
“dataSourceType”: “saql”,
“fieldName”: “installation_date_Year~~~installation_date_Year”
}
],
“label”: “DateYear”,
“name”: “Link_TEST”
},
Hope that helps.
Hi Rikke, love your blog! I’m wondering if this article is still relevant for the Winter ’18 release/changes…I’ve tried using your instructions several times and continue to get the same error Stephane mentioned: “This widget can’t be displayed because there is a problem with its source step:
a.replace is not a function”. I’m just working with a groupings toggle but can’t figure out the correct syntax when using Year-Month or Year-Month-Day in the values field of my toggle – I think the issue is in my static step over the binding in my other step. Any feedback or help would be tremendously appreciated!
Hi Abby, Thanks for the feedback. This is still relevant. I just tried to redo it and it somewhat works. However, I noticed that if I put it in one line – one string – then I get the error too. If I create a separate lens, do a date grouping and a date filter, copy the logic in maintaining the several lines it works. Remember you don’t want the first set of brackets. As an example, I had this grouping in my lens:
“groups”: [
[
“CloseDate_Year”,
“CloseDate_Month”
]
],
I copied this in:
[
“CloseDate_Year”,
“CloseDate_Month”
]
I then got:
{
“display”: “Year-Month”,
“value”: [
“CloseDate_Year”,
“CloseDate_Month”
]
},
That should work…
This is the only helpful article I’ve found on grouping by dates using a static step, but I’m also having the same issue as a few other comments above. I’ve gotten the toggle to work by year, by quarter, or by month, but when I want to do it by both year and quarter, I keep getting an error and I can’t figure out how to write that code correctly.
The “By Year” grouping (which works) shows:
“display”: “By Year”,
“value”: “datefield__c_year”
For the “By Quarter” grouping, I’ve tried what you’ve suggested:
“display”: “By Quarter”,
“value”: [“datefield__c_year”, “datefield__c_quarter”]
But it doesn’t work! And I’ve spent far too many hours trying to figure this out. Any ideas?
Hi Chelsey,
Thanks for the feedback. Because of the issues so many have had – and commented on here – I tried to do it again. It seems that one of the last releases have changed the way formatting is done when using the static step creator within the dashboard builder.
What I did to make it work is create my static step with the builder but just put something random in the value input. I had to go into my dashboard JSON and add the grouping myself. So I had a lens open with my grouping, I copied the JSON for that grouping and pasted it into my static step on my dashboard JSON. The issue I found was it seems to want to have each grouping in a new line and with specific indent.
I’ll try and do a little video this weekend to make it more clear the “hack” I did to overcome this “feature”. In the meantime, I hope this helps.
As promised I looked at a fix… have a look at this one: https://www.salesforceblogger.com/2017/11/19/hack-for-date-groupings-in-static-steps/
Rikke, Thank you v much for this – best reference I could find on relative dates in static filters.
Hi Rikke – are you able to apply multiple static steps as filters? I have been attempting to do so, but not successful thus far.
Depends what you are trying to do. Compact form may be tricky, but should be doable in SAQL
Hey,
Loved your blog,
I would be obliged if you can help me with this error.
This widget can’t be displayed because of a problem with the underlying query, lens_2:
Cannot read properties of undefined (reading ‘getAccessFunction’)
Im having the same issue as well. not sure if this is working anymore