Making Complex Conditional Formatting EASY!
In the last blog post about Conditional Formatting, we learned about the awesome use of doing some easy conditional formatting in tables. But, as it happens, real-world business use cases are rarely as simple! That makes conditional formatting tougher. Let’s take a look at some use cases:
- Based on a hidden measure/dimension.
- Based on a hidden or static step. Think coloring a column based on the variance that the user chooses.
- Based on more than one dimension or a combo of measure and dimension.
Dive-in
Let’s dive straight in without much ado. We will use the same taxi dataset as usual and work on the conditional formatting based on the above use cases. Caveat: The above cases are possible in compareTables only.
Case 1: Color the Pickup Community Area based on Tips.
We start by creating a new dashboard. Name it and then format the dashboard as per our liking. Then click Create Query and create a new compareTable widget with the following dimensions and measures.
- Pickup Community Area is the dimension
- Sum Trip Total is measure 1. Name it as Trip_Total
- Sum Tips is measure 2. Name it as Tips.
Now, click on the drop-down arrow beside Tips and hide it. Now we will use this hidden field to color our Community Area.
Click on the Formatting icon on the right side of the widget/lens and then click on columns. Then choose the column as ‘Pickup Community Area’ and then click on Conditional Formatting.
Next, click Add Rule and in the reference-column select the hidden column. In this case that will the Tips. Then either specify bins or choose greater than/less than or any other available coloring method. Lastly, click apply! Viola! We now have our required column colored using a hidden column!
Case 2: Color the Pickup Time based on the change in the number of rides.
To do this, let’s start by adding a static step and call it Change (API: Change_1) and add it to a list widget. Then change the selection type of the widget to “Single-Selection Required”. We will use this step to bind to the compareTable so that as the user changes the value of the static step, the conditional formatting of the Pickup Time will change! Note the header field names in the image below.
We start the same way we did for Case 1. Create Query. Convert the chart widget to a compareTable. Then group by Pickup Community Area and Start Time Hour. Now, for change from previous, we will use the windowing function. Add a new measure column and name the Column Alias and Column Header as Change (keeps everything simple). Then using f(x) from the calculation part, select Change from Previous. In the Reset-Group select Pickup Community Area. You will see that the format below has now changed to % (Percent) instead of a number. Then Apply.
Once done, you will notice positive and negative numbers. But we are looking for absolute numbers only. Thus, we turn this to SAQL and add abs and round as new functions.
Before we change SAQL:
q = load "taxi_trips";
result = group q by ('Pickup_Community_Area__c', 'Start_Time__c_Hour');
result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'Start_Time__c_Hour' as 'Start_Time__c_Hour', count(q) as 'A';
result = group result by ('Pickup_Community_Area__c', 'Start_Time__c_Hour');
result = foreach result generate 'Pickup_Community_Area__c', 'Start_Time__c_Hour', first(A) as 'A', (sum(A) - sum(sum(A)) over ([-1..-1] partition by 'Pickup_Community_Area__c' order by ('Pickup_Community_Area__c','Start_Time__c_Hour')))/(sum(sum(A)) over ([-1..-1] partition by 'Pickup_Community_Area__c' order by ('Pickup_Community_Area__c','Start_Time__c_Hour'))) as 'Change';
result = order result by ('Pickup_Community_Area__c' asc, 'Start_Time__c_Hour' asc);
result = limit result 2000;After we change SAQL:
q = load "taxi_trips";
result = group q by ('Pickup_Community_Area__c', 'Start_Time__c_Hour');
result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'Start_Time__c_Hour' as 'Start_Time__c_Hour', count(q) as 'A';
result = group result by ('Pickup_Community_Area__c', 'Start_Time__c_Hour');
result = foreach result generate 'Pickup_Community_Area__c', 'Start_Time__c_Hour', first(A) as 'A', abs(round((sum(A) - sum(sum(A)) over ([-1..-1] partition by 'Pickup_Community_Area__c' order by ('Pickup_Community_Area__c','Start_Time__c_Hour')))/(sum(sum(A)) over ([-1..-1] partition by 'Pickup_Community_Area__c' order by ('Pickup_Community_Area__c','Start_Time__c_Hour'))),2)) as 'Change';
result = order result by ('Pickup_Community_Area__c' asc, 'Start_Time__c_Hour' asc);
result = limit result 2000;Notice that abs(round(window function), 2) — This is the basic formula.
Now, all we have to do is bind (interact) the static step with the above step. To do this, we will add a cell asString() binding and use the Change column for conditional formatting.
To add the binding, we can use the advanced editor to create the binding or just add the following before the order statement in JSON (Cmd/Ctrl + E). This statement says that when Change*100 (the value is showing in % but the value is actually not a % thus we are converting the value to a % only for the color part) ≥ the selected change value then color “green” else “red”.
result = foreach result generate ..., case when Change*100 >= {{cell(Change_1.selection, 0, "value").asString()}} then "green" else "red" end as 'Change Color'.The final query will be:
q = load \"taxi_trips\";\nresult = group q by ('Pickup_Community_Area__c', 'Start_Time__c_Hour');\nresult = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'Start_Time__c_Hour' as 'Start_Time__c_Hour', count(q) as 'A';\nresult = group result by ('Pickup_Community_Area__c', 'Start_Time__c_Hour');\nresult = foreach result generate 'Pickup_Community_Area__c', 'Start_Time__c_Hour', first(A) as 'A', abs(round((sum(A) - sum(sum(A)) over ([-1..-1] partition by 'Pickup_Community_Area__c' order by ('Pickup_Community_Area__c','Start_Time__c_Hour')))/(sum(sum(A)) over ([-1..-1] partition by 'Pickup_Community_Area__c' order by ('Pickup_Community_Area__c','Start_Time__c_Hour'))),2)) as 'Change';\nresult = foreach result generate 'Pickup_Community_Area__c', 'Start_Time__c_Hour', A as '#', Change, case when Change*100 >= {{cell(Change_1.selection, 0, \"value\").asString()}} then \"green\" else \"red\" end as 'Change Color';\nresult = order result by ('Pickup_Community_Area__c' asc, 'Start_Time__c_Hour' asc);\nresult = limit result 2000;
This adds a new column to the step. We will hide this from the widget by simply adding the required columns to the “columns” part of the widget under and step part.
"columns": [
"Pickup_Community_Area__c",
"Start_Time__c_Hour",
"#",
"Change"
],
Finally, we will apply the conditional formatting!
Case 3: Color the Taxi Affiliation based on Trip Total and Taxi Affiliation.
For the last case, we will again group the compareTable by Pickup Community Area and Taxi Affiliation (API Name: aff.name) and the sum of Trip_Total for the measure column (Column Alias: Trip_Total and Column Header: Trip Total). Now we will create another column called Color and add a case statement as below:
case when ('aff.Name' matches "Taxi" and Trip_Total > 500) then "blue" when ('aff.Name' matches "Cab" and Trip_Total > 20) then "green" else "grey" endNow, hide the column that we just created and follow the conditional formatting method we did in the above two examples.
There are unending things you can do with conditional formatting. This makes the tables easier to read and more appealing to a visual person. Try it out and you can come up with more use cases!
