Compare Table Presents: Fun with Flags!
So who doesn’t love conditional formatting? Amazing how such a “simple” feature can generate so much excitement. Summer 18 has been out for a few weeks now, so I assume as an Einstein Analytics user you have already played around with the functionality. Of course, it’s great that you can modify the colors in all your charts, but how cool is the conditional formatting in tables? Not only can you do conditional highlighting of cells you can make the condition based on a different column in the table. But did you know you can take conditional formatting even further with compare tables? Well, let’s have a look at how you can calculate different ranges which you then can use as conditions for the color coding. Or in other words Compare Table Presents: Fun with Flags!
From your dashboard start by creating a step in your dashboard and switch to compare table mode in the top right corner.
Once you have the compare table make sure to change ‘Count of Rows’ to ‘Sum of Amount’ and clone the column.
With the measures added it’s time to add a few groupings; I’ve chosen Account Name, Account Type, and Account Industry.
Next, we need to modify our second ‘Sum of Amount’ by clicking on the arrow and choosing ‘Edit this Column’.
Now you can edit the column in the right side. First, we just want to give our column a name. I am calling mine ‘Flag’. Notice that you can give the column an alias as well as a header, the difference is that the alias is what is referenced in the JSON instructions instead of ‘A’ or ‘B’ where the header is what the end user will see.
In the calculation section, we will add a case statement. This is really where you can be creative with your conditional formatting, so make sure you know the criteria for the business logic. In my example, I highly doubt anyone will find the criteria helpful, but hey I am just showing yet another reason why compare tables are absolutely amazing. Anyway, here’s the case statement I am using:
case when 'AccountId.Type' == "Customer" && 'AccountId.Industry' == "Media" then 0 when 'AccountId.Type' == "Customer" && 'AccountId.Industry' == "Technology" then 1 else 2 end
Okay, so what does it all mean? Well, I have used the case statement to define different criteria such as if account type is ‘customer’ and the industry is ‘media’ then give me the value ‘0’, and if account type is ‘customer’ and the industry is ‘technology’ then return the value ‘1’ and for anything that doesn’t match the defined criteria then give me the value ‘2’. Once you have your case statement then hit ‘Apply’ and watch the column change from an amount to ‘0’, ‘1’ or ‘2’. Finally, close the right editor done by clicking the ‘Close’ button next to the ‘Apply’ button.
Now we want to add the conditional formatting, so choose the pain roll to open up the formatting editor and click on the ‘Column’ tab.
From here you want to first choose the column you want to format. In my case, I want to change the ‘Sum of Amount’. Expand the ‘Conditional Formatting’ section and hit ‘Add Rule’.
It’s now time to use the flag we created. In the ‘Reference Column’ choose your newly created flag. You can now use the values you created when specifying the ranges. So, I’ve just created three bins; <1, 1 to <2, and ≥ 2 that each has its own background color.
Once defined you can hit ‘Apply’ and watch the color being applied to your amount and then you can close down the conditional formatting editor.
Finally, I’m not interested in seeing my flag column since that is not very meaningful for my end user, so I will simply hide the column but maintain the coloring. So next to your flag measure click on the arrow and choose ‘Hide’.
You can now give your step a name and hit ‘Done’ to add your new step to your dashboard. And that’s how you can have fun with flags in compare tabels.
Written by
