The World of Date Bindings (Part 2)

In May 2019, I wrote a blog post about Date Bindings and mentioned a use case for grouping the dates by year, month, quarter, etc.

Here is the case:

A 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 widespread request especially when the data spans over a few years or more.

Let’s get cracking on how to solve this!

Solution

There are two solutions to this.

1. Use recipes to break down Trip Start Time into combo date and time components like week and year, quarter and year, etc. Then group by these like any other group by table/chart functionality. (This group by has already been shown in Part 1 of Date Bindings).
2. But, what if we don’t have access to edit recipes or we don’t want to add more to the recipes? We can still solve this using dashboards and SAQL! This is the solution we will focus on here.

As mentioned, we will use a dashboard to build this out.

We create a chart or table (we are using a chart here) to find the Sum of Trip Totals when grouped by Payment Type and Trip Start Time. This Trip Start Time will be based on the queries we just created above. We also remove the column map portion from the chart in JSON. This will help us group by any column without breaking the chart visualization.

Bar Chart based on Payment Type and Trip Start Time

We haven’t added our new columns yet. Let’s now create a static step and add the Year-Month, Year-Week, Day-Hour as the options.

To do that click on Create Query and select Create Custom Query at the bottom. Then add the columns' names under Display and give the Custom Query a name.

Custom Static Query

Next add dimensions as new columns next to each other. Select the relevant dataset for the dimension and then the relevant column.

Trip Start Timestamp for value_1

Then select the relevant column — Year in this case

Final Create Static Query

Let’s look at how this query looks in JSON.

    "label": "Select Trip Start Time Combo",
    "values": [
        {
            "Display": "Year-Month",
            "value_1": "Trip_Start_Timestamp_Year",
            "value_2": "Trip_Start_Timestamp_Month"
        },
        {
            "Display": "Year-Week",
            "value_1": "Trip_Start_Timestamp_Year",
            "value_2": "Trip_Start_Timestamp_Week"
        },
        {
            "Display": "Day-Hour",
            "value_1": "Trip_Start_Timestamp_Day",
            "value_2": "Trip_Start_Timestamp_Hour"
        }
    ]

Then Add this to a list selector. And give this a title - Select Trip Start Time. We can uncheck Update instantly if we want. Uncheck Show Widget Actions. Then go to Query on the same list selector. Now update the Selection Type to Single selection (required).

Custom Query — Single selection (required)

Now, the fun part which will allow us to select which combo of date/time we want to see the data grouped by!

Let’s go back to the chart query. Edit this and turn it to Query Mode.

q = load "Taxi_Trips";
q = group q by ('Payment_Type', 'Trip_Start_Timestamp_Year', 'Trip_Start_Timestamp_Week');
q = foreach q generate q.'Payment_Type' as 'Payment_Type', q.'Trip_Start_Timestamp_Year' as 'Trip_Start_Timestamp_Year', q.'Trip_Start_Timestamp_Week' as 'Trip_Start_Timestamp_Week', sum(q.'Trip_Total') as 'A';
q = order q by ('Payment_Type' asc, 'Trip_Start_Timestamp_Year' asc, 'Trip_Start_Timestamp_Week' asc);
q = limit q 2000;

We will update the group by, generate, and order statements to replace the Trip_Start_Timestamp portions.

We will use the Advanced Editor on the Chart to update the query above.

Click on the chart and then Advanced Editor. Go to the Query on the window that opens.

Advanced Editor

On the left panel, Select Query is the section that will be used to create the interactions.

Let’s select the Static Step in the Select query part, followed by Interaction Type as Selection and then Source Data as Cell , Row Index as 0 and column as value_1. This will replace the Trip_Start_Timestamp_Year portion in the query above. Copy the created interaction and replace the Trip_Start_Timestamp_Year portion in all places. We also keep the Serialization to asString.

{{cell(Select_Trip_Start_Ti_1.selection, 0, \"value_1\").asString()}}

Next, we repeat this for the second column Trip_Start_Timestamp_Week but use value_2 instead of value_1. The final query will look something like this.

q = load "Taxi_Trips";
q = group q by ('Payment_Type', {{cell(Select_Trip_Start_Ti_1.selection, 0, "value_1").asString()}}, {{cell(Select_Trip_Start_Ti_1.selection, 0, "value_2").asString()}});
q = foreach q generate q.'Payment_Type' as 'Payment_Type', q.{{cell(Select_Trip_Start_Ti_1.selection, 0, "value_1").asString()}} as {{cell(Select_Trip_Start_Ti_1.selection, 0, "value_1").asString()}}, q.{{cell(Select_Trip_Start_Ti_1.selection, 0, "value_2").asString()}} as {{cell(Select_Trip_Start_Ti_1.selection, 0, "value_2").asString()}}, sum(q.'Trip_Total') as 'A';
q = order q by ('Payment_Type' asc, {{cell(Select_Trip_Start_Ti_1.selection, 0, "value_1").asString()}} asc, {{cell(Select_Trip_Start_Ti_1.selection, 0, "value_2").asString()}} asc);
q = limit q 2000;

Final Group By for Date/Time Combo

This method can be used for any combination of Date and Time and is not limited to the 3 we see above.