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 (coming soon).


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.