How to: Show values as a heat map

Show how values spread over two dimensions, such as sign-ins per weekday and hour, as a table whose cells are colored by their value: pale for few, dark for many.

Requires WKL Forms Object Library 1.62 or later.

A crosstab query delivers one row per weekday and one column per hour. A WklGrid shows the query, and one color scale rule (wrkColorScale) per hour column colors each cell between MinColor and MaxColor. Because every rule scales over the values of its own column, the rules get the same fixed ends (MinValue, MaxValue), so the same count has the same color in every column.

Two queries: qrySignInsPerHour counts per weekday and hour, and the crosstab qrySignInHeatMap turns the hours into columns 00 to 23 (with 0 instead of an empty cell).

-- qrySignInsPerHour
SELECT Weekday([SignedInAt], 2) AS WeekDay, HOUR([SignedInAt]) AS HourOfDay, COUNT(*) AS SignIns
FROM tblSignIn
GROUP BY Weekday([SignedInAt], 2), HOUR([SignedInAt]);
 
-- qrySignInHeatMap
TRANSFORM Nz(COUNT([ID]), 0) AS SignIns
SELECT Weekday([SignedInAt], 2) AS WeekDay
FROM tblSignIn
GROUP BY Weekday([SignedInAt], 2)
PIVOT Format(HOUR([SignedInAt]), "00") IN ("00","01","02","03","04","05","06","07","08","09","10","11",
                                           "12","13","14","15","16","17","18","19","20","21","22","23");

Private Sub Form_Load()
    Dim highest As Variant
    Dim h As Integer
    highest = DMax("SignIns", "qrySignInsPerHour")    ' one scale for all columns
    With Me!Grid1.Object
        .RecordSource = "qrySignInHeatMap"
        .KeyField = "WeekDay"
        .FormatRules.Clear
        For h = 0 To 23
            With .FormatRules.Add(Format(h, "00"), wrkColorScale)
                .MinColor = RGB(255, 255, 255)
                .MaxColor = RGB(31, 78, 121)
                .MinValue = 0
                .MaxValue = Nz(highest, 1)
            End With
        Next h
    End With
End Sub

• Without fixed ends, a color scale runs from the smallest to the largest loaded value of its column. That suits a single column; for a heat map over several columns it would color each hour by its own maximum.
• MidColor adds a third color, for example red, yellow and green around a target value (MidValue). With MidColor at -1 (the default), the scale has two colors.
• Cells with a dark background get white text. A ForeColor on the rule overrides that.
• A rule earlier in the list that sets a background wins over the color scale.
• In Windows high contrast mode the color scale draws nothing, so the numbers in the cells must carry the meaning.
• To keep the colors in Excel, export as shown: ExportToXlsx path, wemWysiwyg. The default export (wemDataAware) writes the values only.

• How to: Format cells conditionally
• API reference: WklFormatRule, WklRuleKind
• WklGrid