Welcome to the world of Date Bindings

Let’s take the Chicago Taxi Data that we have been using to create cool things and in the process learn about Einstein Analytics a few steps beyond the usual charts and tables. A very common request that comes up in everyday work is:

1. We want to see the dates by our field of choice and trust me in some cases, there are up to 4 or 5 different date fields!

2. We want to see the dates by date, day, week, month, quarter, year.

3. We want to see the dates by week and year, quarter and year, month and year.

This seemingly small request can sometimes be an ordeal if you don’t understand the complexity of how dates work in Einstein Analytics! So, let’s try solving the above 3 issues using bindings (not one at a time but combining them!) which you might have guessed is the only solution. There is another way you can do this though — that is using pages! In which case, you don’t need any bindings. But since we are limited to just 20 pages and sometimes, 1 dashboard can come with multiple other things that need to go on dashboards, this might not be a choice at all. The other case is when you have to use a combination of 1 and 2 or 1 and 3 above! You can’t have 5–6 pages of just that! Every possible combo has to be thought about and that will be a page on its own.

Problem Statement

Instead of just a single date binding, we will create a case for 1 and 2 and 1 and 3.

Case 1: Client wants to see the number of trips taken by the Trip Start Time and Trip End Time. They also want to be able to change the chart by date, day, week, month.

Case 2 (This will be its own blog on a later date): Client wants to see the number of trips taken by the Trip Start Time and Trip End Time. But they want the charts to change based on week and year, quarter and year, month and year. In this dataset, we don’t have a variation of years. However, this is a very common request especially when the data spans over a couple of years or more.

Solution

• Let’s start the usual way by opening a dashboard, naming and saving it.
• Then sizing the dashboard based on # of columns, cell spacing, background colors, etc.
• Now that the initial layout has been set, let’s create a simple bar chart with Trip Start Timestamp (Month) as the x-axis and count of rows as the y-axis.
• To do this, pull the chart from the left-hand panel of the dashboard, size it as you like and click on the middle of the widget where it reads chart.
• A new screen pops up asking us to choose the dataset.
• In this example, we will choose Taxi_Trips. This leads to the next screen which is a usual lens which we are all familiar about.
• The other way of doing this is to Click on Create Steps, create the lens and pull it into the dashboard when done. Either way, the final output is the same.
• To start with the lens already shows the count of rows.
• All we need to add is a grouping/bars (for bar chart) for the Trip Start Time and we will select months to start with.
• Name the lens. We will call it Trips by Time (label name).
• And then click done!
• Voila, you have the first lens on your dashboard!

# of Trips taken per Month

• Next, we will create 2 static steps.
• First will be for a toggle between Trip Start Time and Trip End Time and the second will be a toggle between date, day, week, month, quarter, year.
• Click on Create Step and add a Static Step called Select_Trip_Time (Step Label Name) with values similar to the one here.

First Select_Trip_Time Static Step

• We will now change the values for this in the JSON. Let’s type Cmd/Ctrl + E as the case may be.
• Find the Step named Select_Trip_Time_1 (API Name) and replace the value for Trip Start Time and Trip End Time as follows (These are the API Names of the fields):

"Select_Trip_Time_1": {
    "type": "staticflex",
    "numbers": [],
    "strings": [],
    "groups": [],
    "broadcastFacet": true,
    "selectMode": "single",
    "label": "Select_Trip_Time",
    "values": [
        {
            "display": "Trip Start Time",
            "value": "Trip_Start_Timestamp"
        },
        {
            "display": "Trip End Time",
            "value": "Trip_End_Timestamp"
        }
    ]
}

• We now place the toggle from the left-hand panel and add this step to the toggle.
• Then pick initial selection — This is a must otherwise, the binding will look for “All” and since All is not a valid value, the lens will fail.
• Click on the ellipsis on the dashboard, click “Pick Initial Selection” and choose either Trip Start Time or Trip End Time.

After adding the Select_Trip_Time Toggle to the dashboard

• Moving on to the next static step. This time, we want to select between date, day, week, month, quarter, year.
• Create a static step similar to Select_Trip_Time and add the following values. Let’s call this step Select_Grouping (label name).

Select_Grouping Static Step

• Add this Select_Grouping static step as a toggle or list selector to the dashboard.
• We can go the route of Pick Initial Selection like we did for Select_Trip_Time above or click on the list selector/toggle for Select_Grouping, click on STEP on the right panel, then under Selection Type change Single Selection to Single Selection (required). This takes the first value of the static step by default. If you want to change this to say, Month, you can use the Pick Initial Selection again.

After the 2 Static Steps have been added to the Dashboard

• We now have to bind the 2 static steps above to the Trips_by_Time widget. But before we do so, let’s look at how the lens is grouped in this widget. This will help us understand what we need the binding to read when done.

"query": {
    "measures": [
        [
            "count",
            "*"
        ]
    ],
    "groups": [
        "Trip_Start_Timestamp_Month"
    ]
},

• The above is the query part of the Trips_by_Time step. We have to mimic the groups part in our binding to read anything similar to that. Say, “Trip_Start_Timestamp_Month” or “Trip_Start_Timestamp_Day” etc.
• Let’s now create a Hidden step and we will call it Hidden_Binding. Click Create Step, select Taxi_Trips and add change the compact form to SAQL by clicking on the SAQL Mode symbol. Name the lens and click Done.
• Let’s pull in the Table from the left-hand panel and add the Hidden_Binding Step to it. This will help us visualize if our bindings in the hidden step are working before we do the final binding to our Trips_by_Time widget. All it currently shows is just the count of rows.
• Now we do the first part of the binding. Again type Cmd/Ctrl + E to go to the JSON and find the step Hidden_Binding_1 (API Name). As you can see, the Grouping is currently by all. But we want it to group by Trip_Start_Timestamp and Trip_End_Timestamp since these will be used in the Bindings.
• So we replace the group by all by the following:

Initial: q = group q by all;
Final: q = group q by (Trip_Start_Timestamp, Trip_End_Timestamp);

• Whenever we have more than one field to be grouped by, all groups must be enclosed within a single parenthesis.
• We will now use a case statement to bind the Select_Trip_Time and Select_Grouping Static Steps to the Hidden_Binding Step. This way we can easily mimic the group that is currently present in Trips_by_Time. The query is as follows (This is only the Trip_Start_Timestamp part Trip_End_Timestamp will be a copy of this with just Start replaced by End. I have also separated this out into lines for ease of reading. In JSON, this should be one line only.):

"query": "q = load \"Taxi_Trips\";\n
q = group q by (Trip_Start_Timestamp, Trip_End_Timestamp);\nq = foreach q generate case 
when \"{{cell(Select_Trip_Time_1.selection,0,\"value\").asString()}}\" == \"Trip_Start_Timestamp\" && \"{{cell(Select_Grouping_1.selection,0,\"value\").asString()}}\" == \"Day\" then \"Trip_Start_Timestamp_Day\" 
when \"{{cell(Select_Trip_Time_1.selection,0,\"value\").asString()}}\" == \"Trip_Start_Timestamp\" && \"{{cell(Select_Grouping_1.selection,0,\"value\").asString()}}\" == \"Date\" then \"Trip_Start_Timestamp\" 
when \"{{cell(Select_Trip_Time_1.selection,0,\"value\").asString()}}\" == \"Trip_Start_Timestamp\" && \"{{cell(Select_Grouping_1.selection,0,\"value\").asString()}}\" == \"Month\" then \"Trip_Start_Timestamp_Month\" 
when \"{{cell(Select_Trip_Time_1.selection,0,\"value\").asString()}}\" == \"Trip_Start_Timestamp\" && \"{{cell(Select_Grouping_1.selection,0,\"value\").asString()}}\" == \"Quarter\" then \"Trip_End_Timestamp_Quarter\" 
when \"{{cell(Select_Trip_Time_1.selection,0,\"value\").asString()}}\" == \"Trip_Start_Timestamp\" && \"{{cell(Select_Grouping_1.selection,0,\"value\").asString()}}\" == \"Week\" then \"Trip_Start_Timestamp_Week\"
 when \"{{cell(Select_Trip_Time_1.selection,0,\"value\").asString()}}\" == \"Trip_Start_Timestamp\" && \"{{cell(Select_Grouping_1.selection,0,\"value\").asString()}}\" == \"Year\" then \"Trip_Start_Timestamp_Year\" 
else \"Trip_End_Timestamp_Month\" end as 'Trip_Time';\n
q = limit q 2000;",

• Breaking down the above query, it reads: When Select_Trip_Time_1 = Trip_Start_Timestamp and Select_Grouping_1 = Day then use Trip_Start_Timestamp_Day. Similarly for all the other case statements within. All case statements end with an else “do something” end. Hence the last part of the query so that it doesn’t fail (It shouldn’t fail in this case because there are no other selections possible.). We are also using asString () instead of asObject() because these values are coming from 2 static steps whose values are strings and not field names (or objects). We are using cell instead of column for the same reason. These are just simple strings! No columns, no fields — nothing! Note that you can also use Trip_Start_Timestamp_Hour if you want to see the chart by Hour of the Day.

Hidden_Binding after the Binding has been performed. Note, the Bar Chart widget isn’t changing because it has not been included in the binding yet.

• Now we bind this Hidden_Binding Step to the Trips_by_Time widget. All we need to do is add the Binding to the group part. The group of the query part now changes to:

"query": {
    "measures": [
        [
            "count",
            "*"
        ]
    ],
    "groups": [
        "{{cell(Hidden_Binding_1.result,0,\"Trip_Time\").asObject()}}"
    ]
},

• Breaking down the binding above, we want to use the field that was created in the Hidden_Binding. Thus, we are using the result of the Hidden_Binding Step. When creating Hidden_Binding, we were selecting the values from 2 Static Steps and hence used selection there. And the field of the Hidden_Binding we are interested in is called “Trip_Time”. Since we want to group by a field and not a string anymore, we use asObject().
• When done, you will see that the chart doesn’t show and instead you get an exclamation mark in yellow! That’s because of columnMap.

columnMaps issue

• In JSON, under the Step section for Trips_by_Time_1 you will find a columnMap section and the same one under the Widget section as well.

"columnMap": {
    "trellis": [],
    "dimensionAxis": [
        "Trip_Start_Timestamp_Month"
    ],
    "plots": [
        "count"
    ]
},

• The reason, the chart doesn’t show is because the dimensionAxis reads Trip_Start_Timestamp_Month which is not always the case. When we bind the steps, instead of Trip_Start_Timestamp_Month it can be Trip_Start_Timestamp_Day as well. And columnMap doesn’t understand binding yet. To prevent this from happening, all we have to do is replace the columnMap section in Trips_by_Time_1 Step and the corresponding widget with the following:

"columns": [],

• And the chart will show up!

Final Dashboard

Final Notes

• What type of binding to use, selection or result? Important to know the difference.
• Similarly, which type of binding, asString, asObject, asEquality?
• What are we trying to be the result of the binding? In our case, it was replicating Trip_Start_Timestamp_Month.
• Dates are their unique genre and the more we work on it, the more we find use cases and solutions.
• If you want to show only the Dates and NOT date/time, then use computeExpression to change it to a Date field in the Dataflow. Easier to make the change there than adding more to the binding!
• Last but not least, read Bindings from Rikke Hovgaard’s blog.