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.


17 thoughts on “Einstein Analytics: Demystifying Bindings – Part 4”

  • Avatar 1
    Rahul Sngh on February 4, 2019 Reply

    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.

    • Avatar 2
      Rikke on February 4, 2019 Reply

      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.

  • Avatar 3
    Eulogio Gallo on April 11, 2019 Reply

    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”]]?

  • Avatar 4
    Cesar on June 13, 2019 Reply

    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?

  • Avatar 5
    hiovany cubillos g on June 25, 2019 Reply

    Hello Rikke Hovgaard,

    The video are missing, plase.

    • Avatar 6
      Rikke on June 25, 2019 Reply

      Sorry I don’t do videos.

  • Avatar 7
    Daniel on August 30, 2019 Reply

    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

    • Avatar 8
      Rikke on August 30, 2019 Reply

      I think I did something like this last year. I need to dig it out…

      • Avatar 9
        Daniel on August 30, 2019 Reply

        Thank you so much! Any little insight is much appreciated!

        Thanks again Rikke!

  • Avatar 10
    Sarang Nanavati on May 12, 2020 Reply

    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?

  • Avatar 11
    Aniqa on August 3, 2020 Reply

    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..

    • Avatar 12
      Rikke on August 5, 2020 Reply

      Mixing different dates I tend to switch to epoch and measure filters instead.

  • Avatar 13
    Eric on August 6, 2020 Reply

    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

  • Avatar 14
    Virginie HM on September 10, 2020 Reply

    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?

    • Avatar 15
      Virginie HM on September 10, 2020 Reply

      Ok, so reworking on the query, not getting this error anymore but now nothing happens when I click on “Run Query” or “Update”.
      Help!

      • Avatar 16
        Virginie HM on September 10, 2020 Reply

        Back to the same error.

        • Avatar 17
          Joseph on September 11, 2020 Reply

          I have exactly the same issue, nothing hapens when hitting “run”
          waht can cause that?

Leave a Reply to Cesar Cancel 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.