Full year calendar
Setup
This query used for this example has the following structure:- Date field - make sure to enable “fill in missing dates” to capture any days that are missing data and to extend to the full year
- Value field - a numeric field that we’ll use to color the day by
- Color calculation - a simple % of the largest value to map colors (
=B1 / MAX(B:B)) - Color bucket calculation - name of the color “bucket” the date fits into so we can have fewer colors to work with (
=IFS(C1 = 0, "empty", C1 < 0.2, "lt20", C1 < 0.4, "lt40", C1 < 0.6, "lt60", C1 < 0.8, "lt80", TRUE, "lt100"))
Example code
The example below is customized to work for 2025. For different years, you’ll need to adjust the number of empty<li> tags to get the data starting on the right day of the week.
Full year calendar, vertical
Setup
The setup for the vertical full year calendar is identical to the example above:- Date field - make sure to enable “fill in missing dates” to capture any days that are missing data and to extend to the full year
- Value field - a numeric field that we’ll use to color the day by
- Color calculation - a simple % of the largest value to map colors (
=B1 / MAX(B:B)) - Color bucket calculation - name of the color “bucket” the date fits into so we can have fewer colors to work with (
=IFS(C1 = 0, "empty", C1 < 0.2, "lt20", C1 < 0.4, "lt40", C1 < 0.6, "lt60", C1 < 0.8, "lt80", TRUE, "lt100"))
Example code
Single month heatmap
Fills a single month like a typical wall calendar, correctly laying out the days of the week no matter the month. Also adds a little• marker on today, if it is in view.
Setup
This example uses a lot of calculations make the drawing of any month possible. When referencing calculations in mustache, you need to use the ID of the field, which will automatically be assigned to the calculation when you create it. For example, the first calc you create will have the ID ofcalc_1, the second calc_2, etc. In the example query, the calc ID has been provided as part of the label so you can more easily connect the references to the calculation in the markdown code. When you create your own calculations, they may have different IDs.
Note: Make sure to have “fill in missing rows” turned on for Date, Day of month and the Day of week columns in order to correctly display the full month.
| Col | Name | Calculation formula | Purpose |
|---|---|---|---|
| A | Date | query data | |
| B | Day of month | query data | Number in the calendar cell |
| C | Day of week num | query data | Need to know which day of week to start on |
| D | adjusted DoW num (calc_3) | =MOD(C1, 7) + 1 | Our data starts counting week on Mondays. This adjusts the start day of the week to Sunday. |
| E | Users | query data | metric used to base color off of |
| F | pct of max (calc_1) | =E1 / MAX(E:E) | create range for easy bucketing |
| G | color class (calc_4) | =IFS(F1 = 0, “empty”, F1 < 0.2, “lt20”, F1 < 0.4, “lt40”, F1 < 0.6, “lt60”, F1 < 0.8, “lt80”, TRUE, “lt100”) | define bucket ranges to simplify coloring |
| H | heading (calc_2) | =TEXT(A1, “mmmm yyyy”) | nice formatting of the month for the heading |
| I | today (calc_5) | =IF(A1 = TODAY(), “today”, "") | which cell to put a little “today” indicator |
