When you are displaying data across two categories in the form of a heat map matrix, there will be times where you want to display the data by “hour of day” and “day of week”, or “hour of day” with “day of month”, for example. In these cases, you generally want your time heat map axis to be sorted by day or time. However, you may find that your axis showing “day of week” is not ordered properly.
To sort the days of the week, you will need to implement a workaround. The workaround involves adding a case statement to apply a number to each day of the week and then sorting on the number assigned.
To do that, you can use a Case Statement step in the data pipeline as shown below, creating a new column called “day of week number”:
Next, you will add a Sort Rows step to sort your column by our newly created “day of week number” column.
Lastly, you can use a Hide Columns step to hide the column as it’s not needed anymore.
And now your heat map axis will be sorted by day of week!
Note: For this sorting trick to sort the heat map properly, you will need to go into the chart settings and select “Unsorted” on the y-axis. For certain charts like the heat map, you can apply additional sorting from the chart setting and they are defaulted to ascending.