Let’s apply some Conditional Formatting
Conditional Formatting in tables (value and compare) is an extremely useful tool to highlight the cell or column based on some conditions. Think about this as the conditional formatting that’s present in Excel but on steroids. The most common use case for Conditional Formatting is using the current column as the reference and, color-coding it based on values (measures or dimensions) of the same column. The other common use case is to color-code a column based on values on a secondary column. Maybe even a column which actually doesn’t appear in the final table. This is called conditional formatting by reference. Conditional Formatting also appears in charts and reference formatting can be used in those cases as well.
Some of the excellent use case that conditional formatting can be used for:
- Variance — 10% variance in green 100% in yellow 500% in red!
- Color Code the column that opens Actions — Very useful to guide users to the right columns. The way to do this is to use a measure column and give the same color to the bins valued >0 and <0. This prevents us from coloring every value in the dataset for that column.
- A column that the user should be concentrating on for that table when there are a lot of columns to look at. Say, the reason for case closure delays column or cases that remain open for more than a certain number of days.
Simple Use Case
We will start with the simple case of using conditional formatting in tables based on the values in the same column. As usual, we will use the taxi dataset which we had loaded and is ready to be used. More details about this dataset can be found in the Pivot Table blog under this publication.
Moving on. We will start by creating a dashboard, saving it to the app we like, formatting the dashboard if needed (adjusting the number of Columns, Row Height, Color of the dashboard, etc.), and finally adding a widget using Create Query where we select taxi_trips as the dataset.
When we start with the widget, it opens as a bar chart showing how many rows are present in the dataset. We will change it to a value table by clicking the Table Mode on the top right and selecting Values Table. This gives us a list of default columns that can be changed in the dataset. More on this in the next blog. This is how the data looks in a value table with no changes made yet.
Now, name the widget (if you don’t name it, one will be created automatically). Naming the widget properly helps find out which one is being used in which widget easily, esp. when there are a lot of widgets in a single dashboard. In other words, this is good practice. Let’s name this Value Table CF No Ref. That’s the label name which converts to Value_Table_CF_No_Re_1 as API Name. We can change the API Name in JSON for more clarity if needed. Click done and add this to the dashboard layout.
Next, we will do some conditional formatting based on Trip Total. We will color all Trip Total cells that are less than 10 red, 25 yellow, and > 25 green. To do this, select the widget in edit mode, click COLUMN on the right pane, and make the following changes as shown in the image below.
Since Trip Total is a measure column, we can change the Data to show as a bar. We won’t do that here, but the image below shows how the Conditional Formatting formats will change. All text related fields above disappear for good reason!
Click on Conditional Formatting then Add Rule. Add rule opens up the part we were talking about earlier about this feature being on steroids! Since, we said this is a simple case and we will color the same column on which the conditions are based, select the following parameters as shown below.
Let’s break this down.
- Apply to this Column: We have already selected this column in the previous step, so we cannot change the column here.
- Reference Column: In this case, we keep the column the same as the column we are applying the colors to. This is where we can change the column name to reference some other column. We will talk about this in a later segment.
- Coloring Method. There are different coloring methods — Bins and ranges. Bins are what we see in the image above. Ranges include Is Equal To, Is Not Equal To, Is Less Than, and so on. To actually color the cell, we need to apply colors to each value/bin/range. Click on each of these and color the Text Color and Background Color.
This is how the widget looks once the colors are applied.
This technique can be used for compareTables as well. Simple and easy but makes a bland dashboard look cooler and pops out important metrics and issues with data!
Using a different Reference Column
This is a simple method as well (There will be a new blog on the complex version of this soon.).
Very similar to what we did above, create a new widget for value table, name it, add it to the dashboard (ideally a new page). And then we will color the Trip Names based on the same Trip Total logic.
From the image above, we can see the only difference is in the first two parameters. The column to be colored has changed from Trip Total to Trip Name and the Reference column is Trip Total because that is what we want the basis to color the Trip Name.
We can add more columns to be colored and displayed. And more bins can be added if that is the need. Ideally, keeping bins to the bare minimum always gives the best results. Adding too many colors makes it visually unappealing to the eyes and actually puts a strain on them!
Caveat
There is a caveat to conditional formatting like everything else.
- Cannot add conditional formatting based on a non-existent column. The column has to be part of the widget, albeit hidden.
- For measure columns, we can select only texts or bars — not both. Though I would love to see both.
- Conditional Formatting is stored in the dashboard XMD. Thus we will not find any reference for this is the table or chart widgets in JSON.
- If we change the name of the widget in JSON, the conditional formatting will disappear and we have to redo the formatting. JSON will give you a warning about this when you try saving the JSON.
