Win with Totals/Sub-Totals in SAQL!
Totals and Sub-Totals have been a request that we Tableau CRM developers must have come across many times! Tableau CRM now has UI-based Totals and Sub-Totals, but that doesn’t solve some use cases. Say, we want to find the Total or Sub-Total for a group with average sales per month or the Total or Sub-Total for the max amount received per month, etc. These are the cases where SAQL comes to our rescue!
Use Case
As we always do, we will take our Chicago taxi data and find the average miles run per hour per day and then find the sub-total by Pickup Community Area per day and then the final total. — Easy enough!
Solution
Let’s start by opening a dashboard, adding a table widget, and grouping the data by Community Area. To do this we will use our taxi_trips dataset and name the query. It is a good practice to name the queries so as to have an accurate representation of what they are meant to do. Let’s call this Totals Average Miles. Turn the widget to a compare table and then group it by the Pickup Community Area, Start Time Day and finally Start Time Hour.
Next, instead of the count of rows, we will update the measure column to average miles.
Now, we change this to SAQL using the query mode when the widget is in edit mode.
Our current SAQL reads:
q = load "taxi_trips";result = group q by ('Pickup_Community_Area__c', 'Start_Time__c_Day', 'Start_Time__c_Hour');result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'Start_Time__c_Day' as 'Start_Time__c_Day', q.'Start_Time__c_Hour' as 'Start_Time__c_Hour', avg(q.'Miles__c') as 'A';result = order result by ('Pickup_Community_Area__c' asc, 'Start_Time__c_Day' asc, 'Start_Time__c_Hour' asc);result = limit result 2000;
A few things, that we can update to make this query more readable are the API Names in the foreach statement. The words highlighted in Bold are the changes made.
q = load "taxi_trips";result = group q by ('Pickup_Community_Area__c', 'Start_Time__c_Day', 'Start_Time__c_Hour');result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'Start_Time__c_Day' as 'Day', q.'Start_Time__c_Hour' as 'Hour', avg(q.'Miles__c') as 'Avg_Miles';result = order result by ('Pickup_Community_Area__c' asc, 'Day' asc, 'Hour' asc);result = limit result 2000;
The table headers now read.
Now, the fun part of totals and sub-totals!
We will create two more streams from the existing query. One we will call subtot for sub-totals and another tot for totals.
Let’s start with sub-totals where we want the sub-totals for Average miles per day based on the Pickup Community Areas.
To do this, we start with another group statement and group it by Pickup Community Area and Day fields. Then we formulate the foreach statement. Point to note here is that, if we want to show the original data (average of miles by hour), then for every field in the initial table, there has to be a corresponding field in this stream as well. Thus, for the Hour column, we add “zz-Sub-Total” as ‘Hour’ and then sum the Avg_Miles as Avg_Miles. We also comment out the order by and limit statement for the time being. The reason to add zz is in case, this the column has alphabets instead of numbers, we don't want the sub-total to be hiding in between the rest of the data. This is a sure way of ensuring that the sub-total will be at the end of the groups.
q = load "taxi_trips";result = group q by ('Pickup_Community_Area__c', 'Start_Time__c_Day', 'Start_Time__c_Hour');result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'Start_Time__c_Day' as 'Day', q.'Start_Time__c_Hour' as 'Hour', avg(q.'Miles__c') as 'Avg_Miles';subtot = group result by ('Pickup_Community_Area__c', 'Day');subtot = foreach subtot generate 'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', 'Day' as 'Day', "zz-Sub-Total" as 'Hour', sum('Avg_Miles') as 'Avg_Miles';-- result = order result by ('Pickup_Community_Area__c' asc, 'Day' asc, 'Hour' asc);-- result = limit result 2000;
Now it’s turn for Total! Very similar to how we did Sub-Totals, we will perform the Total. Now, we will have dummy text values for Pickup Community Area, Day, and Hour.
q = load "taxi_trips";result = group q by ('Pickup_Community_Area__c', 'Start_Time__c_Day', 'Start_Time__c_Hour');result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'Start_Time__c_Day' as 'Day', q.'Start_Time__c_Hour' as 'Hour', avg(q.'Miles__c') as 'Avg_Miles';subtot = group result by ('Pickup_Community_Area__c', 'Day');subtot = foreach subtot generate 'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', 'Day' as 'Day', "zz-Sub-Total" as 'Hour', sum('Avg_Miles') as 'Avg_Miles';tot = group subtot by all;tot = foreach tot generate "zz-Total" as 'Pickup_Community_Area__c', "-" as 'Day', "-" as 'Hour', sum('Avg_Miles') as 'Avg_Miles';-- result = order result by ('Pickup_Community_Area__c' asc, 'Day' asc, 'Hour' asc);-- result = limit result 2000;
Lastly, we bring all the three streams together to form a cohesive table that makes sense. It is as simple as grouping all these streams together, formulating a foreach statement to bring all columns in a sequence!
q = load "taxi_trips";result = group q by ('Pickup_Community_Area__c', 'Start_Time__c_Day', 'Start_Time__c_Hour');result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'Start_Time__c_Day' as 'Day', q.'Start_Time__c_Hour' as 'Hour', avg(q.'Miles__c') as 'Avg_Miles';subtot = group result by ('Pickup_Community_Area__c', 'Day');subtot = foreach subtot generate 'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', 'Day' as 'Day', "zz-Sub-Total" as 'Hour', sum('Avg_Miles') as 'Avg_Miles';tot = group subtot by all;tot = foreach tot generate "zz-Total" as 'Pickup_Community_Area__c', "-" as 'Day', "-" as 'Hour', sum('Avg_Miles') as 'Avg_Miles';final = group result by ('Pickup_Community_Area__c', 'Day', 'Hour') full, subtot by ('Pickup_Community_Area__c', 'Day', 'Hour') full, tot by ('Pickup_Community_Area__c', 'Day', 'Hour');final = foreach final generate coalesce(result.'Pickup_Community_Area__c', subtot.'Pickup_Community_Area__c', tot.'Pickup_Community_Area__c') as 'Pickup_Community_Area__c', coalesce(result.'Day', subtot.'Day', tot.'Day') as 'Day', coalesce(result.'Hour', subtot.'Hour', tot.'Hour') as 'Hour', coalesce(sum(result.'Avg_Miles'), sum(subtot.'Avg_Miles'), sum(tot.'Avg_Miles')) as 'Miles';final = order final by ('Pickup_Community_Area__c' asc, 'Day' asc, 'Hour' asc);final = limit final 2000;
From the table below, we can now see all the hours along with the sub-totals and totals!
Few Comments
- This method is not limited to totals and sub-totals. It can be used for anything like the sum of max, max of sum, min of avg, avg of min, etc.
- If we don’t want the detail rows to be shown, remove the first stream from the final group and that will show Sub-Total by Days.
3. And if we want only the final result of totals, we just show the last stream!
Viola!! Now, we have Totals, Sub-Totals, or any other mathematical calculations that we want to perform on groups and then the sub-groups!
