Sort and customize Heat Map charts

A Heat Map is a two-dimensional representation of data in which values are represented by colors. A simple Heat Map provides an immediate visual summary of information. More elaborate Heat Maps allow the viewer to understand complex datasets. There can be many ways to display Heat Maps, but they all share one thing in common—they use color to communicate relationships between data values that would be much harder to understand if presented numerically in a spreadsheet.

Custom sorting for the Heat Map columns

When displaying data across two categories in the form of a Heat Map matrix, you may want to display the data by time—for example, by “hour of day” with “day of week”, or by “hour of day” with “day of month.”

In these cases, you generally want your Heat Map’s y-axis to be sorted by day or time; however, you may find that your axis showing “day of week” is not ordered properly.

The potential quick fix

Open the chart settings, go to the Axis tab, then change the Sort of the Y Axis to Unsorted.

Didn’t solve the issue? No worries—there’s another way to get your Heat Map sorted.

Sort in the Pipeline

You’ll need to create an intermediate step that allows you to sort in your desired order. This involves adding a CASE statement to apply a number to each day of the week then sorting on the assigned numbers.

• In Visual SQL
• In Data Explorer

In the Visual SQL interface

1. Add a Formula Column, select Custom for the formula type, then use the following CASE statement as the formula, replacing Day of Week: Created Date with the name of your “day of week” column:

    case
        when "Day of Week: Created Date"='Sunday' then 1
        when "Day of Week: Created Date"='Monday' then 2
        when "Day of Week: Created Date"='Tuesday' then 3
        when "Day of Week: Created Date"='Wednesday' then 4
        when "Day of Week: Created Date"='Thursday' then 5
        when "Day of Week: Created Date"='Friday' then 6
        when "Day of Week: Created Date"='Saturday' then 7
        else 0
    end
   

   

2. Next, use a Sort Rows Action to sort the new column in ascending order.

3. Lastly, hide the Formula Column—you don’t need it anymore.

Your Heat Map should now be sorted in your desired order. This trick doesn’t just work for Heat Maps; it also works in other instances where you’d like to apply a custom sort order.

Note: You may still need to go to the chart settings and change the sort setting of the Y Axis, as described in the quick fix above.

In the Data Explorer interface

1. Use a Case Statement step to add a new column, give it the name day of week number, and create the cases as shown in the screenshot below.

   

2. Next, use a Sort Rows step to sort the new column in ascending order.

   

3. Lastly, hide the “day of week number” column as it’s not needed once the sort has been completed.

   

Your Heat Map should now be sorted in your desired order. This trick doesn’t just work for Heat Maps; it also works in other instances where you’d like to apply a custom sort order.

Note: You may still need to go to the chart settings and change the sort setting of the Y Axis, as described in the quick fix above.

More Heat Map customizations

To further customize your Heat Map, you can open the Chart Settings and edit the various available options, such as the color spectrum, the background map colors, X-axis and Y-axis titles, detailed annotation labels, and more.