Finally, the summer 18 release here and it’s time to make the most out of the new features. Rikky Hoovgard wrote a great overview of the new features you have available now. Conditional formatting is probably the one new feature you are going to use on a daily base. Let’s see what we can do with it and how we can apply best practices.

In several projects, I came across the requirements to create pivot like tables with totals and subtotal. In the past, you had to find workarounds to make subtotals and totals look different. I used indentations and arrows (↳) to create the impression of a hierarchy. This just got a lot easier with Conditional Formatting. Now we can format each column depending on a reference column.

Create Subtotals and Totals in the Compare Table

***Winter 20 Update: You can create a Subtotal now in the UI with the rollup function. The following is an alternative pre Winter 20 release approach***

I created a quick use case where I use “Industries” as top grouping and “Countries” as a second grouping. I started to create my raw draft in the UI of the compare table.

Once I created my table structure I switched into SAQL mode to create the subtotals. (Carl Brundage made a quick video on how to create subtotals if you want to go into more details).

q = load "Meetup2";
q_A = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["1 month ago".."current month"];
q_B = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["2 months ago".."1 month ago"];
q_C = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["3 months ago".."2 months ago"];
result = group q_A by ('Account.Industry', 'Account.BillingCountry') full, q_B by ('Account.Industry', 'Account.BillingCountry') full, q_C by ('Account.Industry', 'Account.BillingCountry');
result = foreach result generate coalesce(q_A.'Account.Industry', q_B.'Account.Industry', q_C.'Account.Industry') as 'Account.Industry', coalesce(q_A.'Account.BillingCountry', q_B.'Account.BillingCountry', q_C.'Account.BillingCountry') as 'Account.BillingCountry', sum(q_A.'Amount') as 'A', sum(q_B.'Amount') as 'B', sum(q_C.'Amount') as 'C';
result = order result by ('Account.Industry' asc, 'Account.BillingCountry' asc);

Basically, you need to create 2 additional streams one for the subtotals and one for total. After the union and the final projection you get your unformatted list. To make the formatting work in the end I added a “Ref” Reference Column and “SortOrder” in the query. 

The “Ref” Reference column is used as bin-value in the conditional formatting. In my use case, I only have “1” for every “Subtotal” or “2” for “Total” as value. To get the Subtotals in the correct order I integrated two different Sort Orders “SortOrderL1” and “SortOrderL2”  so we can sort in the different groupings. The Values are simply “A” and “B”, as they will be always before “-” empty rows if sorted ascending.

q = load "Meetup2";
q_A = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["1 month ago".."current month"];
q_B = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["2 months ago".."1 month ago"];
q_C = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["3 months ago".."2 months ago"];
result = group q_A by ('Account.Industry', 'Account.BillingCountry') full, q_B by ('Account.Industry', 'Account.BillingCountry') full, q_C by ('Account.Industry', 'Account.BillingCountry');
result = foreach result generate coalesce(q_A.'Account.Industry', q_B.'Account.Industry', q_C.'Account.Industry') as 'Account.Industry', coalesce(q_A.'Account.BillingCountry', q_B.'Account.BillingCountry', q_C.'Account.BillingCountry') as 'Account.BillingCountry', sum(q_A.'Amount') as 'A', sum(q_B.'Amount') as 'B', sum(q_C.'Amount') as 'C';
result = order result by ('Account.Industry' asc, 'Account.BillingCountry' asc);

--/////////////Calculate the Subtotals for each Industry/////////
subtotal = group q_A by 'Account.Industry' full, q_B by ('Account.Industry') full, q_C by ('Account.Industry');
subtotal = foreach subtotal generate coalesce(q_A.'Account.Industry', q_B.'Account.Industry', q_C.'Account.Industry') as 'Account.Industry', sum(q_A.'Amount') as 'A', sum(q_B.'Amount') as 'B', sum(q_C.'Amount') as 'C',"Subtotal "+ coalesce(q_A.'Account.Industry', q_B.'Account.Industry', q_C.'Account.Industry') as 'Account.BillingCountry',1 as 'Ref',"B" as 'SortOrderL1';

--/////////////Calculate the Total Sum for each Industry/////////
grandtotal = group q_A by all full, q_B by all full, q_C by all;
grandtotal = foreach grandtotal generate "Total" as 'Account.Industry' ,sum(q_A.'Amount') as 'A', sum(q_B.'Amount') as 'B', sum(q_C.'Amount') as 'C', "A" as 'SortOrderL2', 2 as 'Ref';

--/////////////Append all three streams/////////
total = union result, subtotal,grandtotal;

--/////////////final query to create the list in the correct sort order/////////
total = group total by ('Account.Industry', 'Account.BillingCountry');
total= foreach total generate total.'Account.Industry' as 'Account.Industry', total.'Account.BillingCountry' as 'Account.BillingCountry', sum(total.'A') as 'A', sum(total.'B') as 'B',sum(total.'C') as 'C',sum(total.'Ref') as 'Ref', last('SortOrderL1') as 'SortOrderL1',last('SortOrderL2') as 'SortOrderL2';
total= order total by ('SortOrderL2' asc,'Account.Industry' desc,'SortOrderL1' asc);

Conditional Formatting in Compare Table

Once your SAQL query is ready you can switch back to edit mode and edit the Conditional Formatting setting. Under the tab Column on the right side, you have now the Option Conditional Formatting on the bottom.

Here you will find several options for the Coloring Method. In this use case, I used “Specific Range (Bins)” so I’m always able to add more Bins if needed. For each column, I needed to individually set the bins and the formatting. As Reference Column I used the calculated field “Ref” which contains my both Bin values 1 and 2.

After the Formatting is done I have 3 columns I don’t wanna actually show as they are just supporting fields. The Compare table helps me here again and I can simply “Hide” my unwanted columns.

As a final result, you get a visually easy understandable table with clear distinction of your aggregation levels and detail information.

From there it takes just a few steps to really shine with your new table and help the user to easily spot differences in hierarchy and value. Somehow tables are fun again:)

Combined with the right bindings and a navigation bar the formatted table can be a powerful tool to create a data-rich interactive chart which is easy to understand. One limitation we have to consider here is that the bar length is individually per column and not overall columns which might lead to misinterpretation of the data. If you want to see how you can scale the bars dynamically to the total of the whole table click here.