Einstein Analytics: Demystifying Bindings – Part 4
In this blog series, we have already covered a lot of ground looking at the anatomy of a binding and the different data serialization functions available. So it’s time to look at some practical examples. A lot of people including myself find date bindings to be a little difficult as there are so many different use cases and the bindings are slightly different in each one of them. We already looked at one use case in the second part of this blog series where the start and end date is powered by the same static step so I will leave that one out, but there are still a few common ones to cover. Please do remember that in some use cases you can avoid using bindings by bucketing your dates in the data layer – in fact, recipes make it very easy to do this.
Binding with a start and an end static step
The use case in this example is to have two static steps; one that controls the start date and one that controls the end date. These two dates are then used in a filter of a chart. The step that is being powered by the binding is pretty simple as there is a single date grouping and sum of Amount. You can see the SAQL step below.
q = load "DTC_Opportunity_SAMPLE";
q = group q by ('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day');
q = foreach q generate 'Close_Date_Year' + "~~~" + 'Close_Date_Month' + "~~~" + 'Close_Date_Day' as 'Close_Date_Year~~~Close_Date_Month~~~Close_Date_Day', sum('Amount') as 'sum_Amount';
q = order q by 'Close_Date_Year~~~Close_Date_Month~~~Close_Date_Day' asc;
q = limit q 2000;
As mentioned above we want to add a filter powered by two static steps. Below are the two static steps one for the start date called StaticStart_1 and one for the end date called StaticEnd_1. Each one of the static steps allows you to select “current day”, “30 days ago” and “90 days ago”. Please make sure to select a start value for both of your steps as you otherwise would get an error later in your binding.
"StaticStart_1": {
"broadcastFacet": true,
"label": "StaticStart",
"selectMode": "singlerequired",
"start": {
"display": [
"30 Days Ago"
]
},
"type": "staticflex",
"values": [
{
"display": "Current Day",
"value": "current day"
},
{
"display": "30 Days Ago",
"value": "30 days ago"
},
{
"display": "90 Days Ago",
"value": "90 days ago"
}
]
},
"StaticEnd_1": {
"broadcastFacet": true,
"label": "StaticEnd",
"selectMode": "singlerequired",
"start": {
"display": [
"Current Day"
]
},
"type": "staticflex",
"values": [
{
"display": "Current Day",
"value": "current day"
},
{
"display": "30 Days Ago",
"value": "30 days ago"
},
{
"display": "90 Days Ago",
"value": "90 days ago"
}
]
}
}
Now when we know the step we want to show in a chart as well as our static step representing the timeframe we can start looking at the binding. First of all, a filter in SAQL would look something like below. This would be right after the load statement in the SAQL.
q = filter q by date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day') in ["30 days ago".."current day"];
Looking at above filter what we want to change is the “30 days ago” and the “current day” with our static start and end step. Hence we need to add two bindings. Let’s first have a look at the “min” value or the start day which in the above example is the “30 days ago” part.
q = filter q by date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day') in ["{{cell(StaticStart_1.selection, 0, "value").asString()}}".."current day"];
As you can see I’ve used the basic cell() data selection binding example described in the first part this blog series. But we have one more thing left to do. We also need to add in a “max” value or the end date. As you can see below we have just replaced the “current day” with a binding.
q = filter q by date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day') in ["{{cell(StaticStart_1.selection, 0, "value").asString()}}".."{{cell(StaticEnd_1.selection, 0, "value").asString()}}"];
Again we have used a basic cell data selection binding to replace the “current” day with the end date that is being selected by the user. We have kept the two dots between the two bindings as this is the syntax of the filter. This whole SAQL should look something like below.
q = load "DTC_Opportunity_SAMPLE";
q = filter q by date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day') in ["{{cell(StaticStart_1.selection, 0, "value").asString()}}".."{{cell(StaticEnd_1.selection, 0, "value").asString()}}"];
q = group q by ('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day');
q = foreach q generate 'Close_Date_Year' + "~~~" + 'Close_Date_Month' + "~~~" + 'Close_Date_Day' as 'Close_Date_Year~~~Close_Date_Month~~~Close_Date_Day', sum('Amount') as 'sum_Amount';
q = order q by 'Close_Date_Year~~~Close_Date_Month~~~Close_Date_Day' asc;
q = limit q 2000;
Now if you preview the dashboard you will see that you can select the start and the end date as you please and the timeframe of the chart will change accordingly to the selections.
Binding with a date widget
The next use case we will be looking at is using the output from a date widget. Now we briefly looked at it in the second part of this blog series, but I wanted to do a deep dive and explain more in details what we are doing. For simplicity let’s use the same SAQL as in the previous example.
q = load "DTC_Opportunity_SAMPLE";
q = group q by ('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day');
q = foreach q generate 'Close_Date_Year' + "~~~" + 'Close_Date_Month' + "~~~" + 'Close_Date_Day' as 'Close_Date_Year~~~Close_Date_Month~~~Close_Date_Day', sum('Amount') as 'sum_Amount';
q = order q by 'Close_Date_Year~~~Close_Date_Month~~~Close_Date_Day' asc;
q = limit q 2000;
Again we want to add a filter in but this time it will come from a date widget. The easiest way to create a date widget is to drag over the date widget from the left-hand-side while in edit mode of your dashboard.
Once you have your date widget you can click on the square inside the widget where it says “Date”. Now you are able to select a date from your chosen dataset.
This will automatically create a new underlying step for your date widget. Notice that you will not be able to edit this nor change the API name of this step. This is expected behavior as we are dealing with the full date and not the date part which we would have used as filters or groups in other steps. Of course when it comes to writing the binding we need to memorize the API name that has been given automatically. In the JSON the query will look like below.
"query": {
"measures": [
[
"count",
"*"
]
],
"groups": [
"Close Date"
]
},
Now we need to add in the filter. Again we are looking at replicating a filter like below via a binding.
q = filter q by date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day') in ["30 days ago".."current day"];
Before we look at the binding it’s important to note that the output of the date widget is “min” and “max” or in other words a start and an end value. However, when we want to reference these output values there are called “min” and “max”. Since we need to take two columns from the same output table we need to use the row() data selection function, which we covered in part 2. The binding would look like below.
q = filter q by {{row(Close_Date_1.selection, [0], ["min", "max"]).asDateRange("date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day')")}};
In this case, the [0] is referencing the row of the table we want to use, which when we set it to 0 is the very first row. The “min” and the “max” is the output from the reference step that we have selected. And finally, we have used the data serialization .asDateRange() to make sure we get the output in the format of [“30 days ago”..”current day”]. Also, notice within the data serialization we define the date field or column we want to apply the filter to. The whole SAQL will look like below.
q = load "DTC_Opportunity_SAMPLE";
q = filter q by {{row(Close_Date_1.selection, [0], ["min", "max"]).asDateRange("date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day')")}};
q = group q by ('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day');
q = foreach q generate 'Close_Date_Year' + "~~~" + 'Close_Date_Month' + "~~~" + 'Close_Date_Day' as 'Close_Date_Year~~~Close_Date_Month~~~Close_Date_Day', sum('Amount') as 'sum_Amount';
q = order q by 'Close_Date_Year~~~Close_Date_Month~~~Close_Date_Day' asc;
q = limit q 2000;
Looking at the dashboard the user can select the timeframe in the date widget and it will be applied to your step as a filter.
More date bindings?
In this blog, you have seen two typical examples of date bindings, let me know if there are other use cases, maybe that can be covered in another blog.
Next up we will be looking at nested bindings.
Written by
Thanks for these great blogs, Rikkie. I have learned a lot from these.
I am just wondering on one point of this blog.
How did you come to know that the output of the date widget is “min” and “max”? The result of the SAQL behind the date widget doesn’t have min and max.
Not sure I understand. But if you create a binding in a text widget where the source step is a date widget then you’ll see it.
This was very helpful, thank you for posting!
What if I wanted to apply a row.asDateRange filter where there is no lower limit? I’m trying to get just the max of a date filter widget, how would I do that so I’m checking if StartDate in [.. row(Widget.selection, [0], [“max”]]?
Hi Rikke,
great article! I checked the documentation for Date Picker binding https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_bindings.meta/bi_dev_guide_bindings/bi_dbjson_relative_dates.htm
The script looks different from yours. I tired to replicate your practice case – no chance. Do you know if any of the newest changes in SF Analytics could break down your solution?
Hello Rikke Hovgaard,
The video are missing, plase.
Sorry I don’t do videos.
Hi Rikke,
I can’t thank you enough for all the amazing content you put up! It really really helps me out a lot! I do appreciate all you efforts! Thank you so much!
I was wondering if you could help me understand how to manipulate dates. I have a requirement where based on the date selected, I need to show information for dates 2 months prior to the selected dates. For example, If the user selects “Current Month” (August) then I need to show results for April, May and June.
I have currently implemented this by using the “max” epoch and extracting the month and year and calculating accordingly. But this works only when they select dates in the “absolute” format eg: 30/8/2019. How do I extract the month and year information for the date widget when it’s format is “30 days ago”..”current day” or [[“month”,-3],[“month”,-2]]? I believe this could be very complex. Is there any simpler way to do this?
Thanks in advance for your help!
Kind regards,
Daniel
I think I did something like this last year. I need to dig it out…
Thank you so much! Any little insight is much appreciated!
Thanks again Rikke!
is there a comparable compact Json? I have a full stack of widgets that’s using compact Json with bindings and I’d like to add the date picker as another binding criteria w/o having to switch compact to SAQL. Any way to achieve that?
Rikke – first off – echo the appreciation on really great and much needed content. I have a use case where the Date Filter is based on one Date Value say EndDate and I need to use the output of the date filter to filter a component but using a second Date Value – Date_of_Joining. What is the syntax I need to incorporate? I just need to pull out the selection of the Date Filter and have it be a part of the other components filter – but can’t figure out the syntax..
Mixing different dates I tend to switch to epoch and measure filters instead.
I’m not seeing this anywhere at the moment. Our use case is to change the Grouping by the Date. Ie., Week/Month/Quarter so the business user can view various metrics based upon Create or Close date. However, when creating the Interaction it displays the SAQL as expected but always errors out on updating the chart. Is this an Einstein limitation or are we just not that skilled (likely).
Thanks
I’m trying this for a client. All was going well until I got the “Message not in execution context.”
Here is my query:
q = load “AppendInvoiceInventory”;
q = filter q by {{row(CreatedDate_2.selection, [0], [“min”, “max”]).asDateRange(“date(‘CreatedDate_Year’, ‘CreatedDate_Month’, ‘CreatedDate_Day’)”)}};
q = group q by (‘CreatedDate_Year’, ‘CreatedDate_Month’, ‘CreatedDate_Day’);
q = foreach q generate ‘CreatedDate_Year’ + “~~~” + ‘CreatedDate_Month’ + “~~~” + ‘CreatedDate_Day’ as ‘CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day’, count() as ‘count’;
q = limit q 2000;
Do you know what causes this error message?
Ok, so reworking on the query, not getting this error anymore but now nothing happens when I click on “Run Query” or “Update”.
Help!
Back to the same error.
I have exactly the same issue, nothing hapens when hitting “run”
waht can cause that?
Thanks Rikke!
I have tried replicating the steps, but I keep receiving the follow error “The bindings of this query reference invalid queries: SendDate_1”. I have double checked the API name and inputs, do you know why this is the case?
I experience the same error. Did you manage to solve it?
Apparently, you can only add the filter binding by editing the JSON. You can’t add it to the query using Query Mode. Once you edit the query in the JSON you can’t go back to Query Mode.
Remember to escape your quotes. Here’s an example (myStaticQuery has 2 columns, myDisplay and myValue):
“DTC_Opportunity_quer_1”: {
…
“query”: “q = load \”DTC_Opportunity_SAMPLE\”;\n*q = filter q by cell(myStaticQuery.selection, 0, \”myValue\”).asEquality(\”Industry\”);*\nq = group q by…
Thank you for posting this. This is super annoying that it works this way but your tip helped get it working for me!
I keep getting an error that my binding is referencing a step that doesn’t exist. I’ve tried using every ID possible for the date selector widget’s reference…. nothing works. Any ideas?
Appreciate any help!
Great content, thank you so much
I have a similar use case and your input would be of great help. I have a dashboard where I track multiple KPIs. I want to filter it based on custom dates and keep it general. For example, I am tracking leads created and leads closed, both have their own date column, lead created date and lead closed date. I want to filter both in order to get the number of closed details and the number of created deals in a range decided by the user.
In the use case you discussed, the date bind is tied to the close date. I want a generic date filter that could be used to filter any KPI. Thank you in advance
Hi Rikkie,
It was very useful article thank you for sharing such complex and dynamic use cases.
I have some similar use case one like you have mentioned in first use case. The difference is that I have Start_Date and End_Date which belongs to dataset A. And I have a date field Snapshot_Date in dataset B. I want to filter a chart of this dataset B based on the Start_Date and End_Date of dataset A.
Can we achieve it by using your solution –> q = filter q by date(‘Close_Date_Year’, ‘Close_Date_Month’, ‘Close_Date_Day’) in [“{{cell(StaticStart_1.selection, 0, “value”).asString()}}”..”{{cell(StaticEnd_1.selection, 0, “value”).asString()}}”];
Thanks in advance.
Hi,
I have a requirement where I’m using a Date widget as a binding to filter a table column. I’m using binding {{row(Close_Date_1.selection, [0], [“min”, “max”]).asDateRange(“date(‘Close_Date_Year’, ‘Close_Date_Month’, ‘Close_Date_Day’)”)}};
However, I need to also automatically use that date to be a previous year filter on another column, is there a way to use and filter from same Date widget automatically by previous year?
I’ve tried having 2 separate date widgets and binding for each but I’m told this is not a good user experience.
i have exactly same issue .
need to autoamatically filter two filter rows in saql by one action .
like if i select by date widget selecting absolute date
[ 24.02.29 ~ 24.03.31 ]
saql binding work like
q_curr = filter q_curr 24.02.29 ~ 24.03.31
q_last = filter q_last 23.02.28 (?) ~ 24.03.31
have you solved that issue?
Oh my God! I have been trying to make the date bindings work on an aggregateflex query since 2019. I am so glad you were able to figure this out. Now I don’t need to switch to SAQL when using bindings. SAQL limits the user functionality in the UI when they explore the lens. That is why I prefer aggregateflex queries.
Thank you so much Rikkie. You are awesome!
Joel Rodriguez