Many of Tiller’s most popular dashboards include organize categories into a type-group-category hierarchy for user-friendly browsing. You might recognize this hierarchy from templates like the Monthly Budget, the Savings Budget, and the Yearly Budget.
If you have ever tried to build this type of hierarchy into your own templates, you know that the implementation is pretty fussy.
Inspired by some encouragement by @jpfieber, I refactored the formulas we use to implement this hierarchy for the soon-to-be-released, refreshed Spending Insights dashboard. With some recent formulas additions (e.g.
MAP, etc), it is “easier than ever” to organize your visualizations in this way (though still not “easy”). All of the key formulas have been implemented as array formulas so a single formula can scale with your data rather than expanding repeating formulas down to the bottom of the sheet.
If you have any suggestions on how to make this better, I’d love to hear them in this thread!
I built the formulas for modularity and reuse in a new demo sheet here.
What am I seeing here? There are a few sections to this example:
B:C- rendered categories for the dashboard
F:H- column lookups and settings
J:N- (raw) query data
P:R- type-group-category row indices
S:V- sorted and filtered data for dashboard
Let’s go into detail on these sections in the order they layer to build the dashboard.
The Settings section contains a single checkbox setting “More Space” that toggles the amount of spacing between the groups and types. This toggle is referenced in a few formulas throughout the sheet. Instead of referencing this settings cell, the setting can be hardcoded in the referencing formulas if you prefer.
For this example, we are pulling data from the
Categories sheets. Depending on what you are trying to display (accounts, tags, transactions, etc), your column lookup needs may be different.
For this example, we are pulling transaction totals for a data range driven by these cells. Again, depending on your needs, this may not be required.
The primary query is in
M2. You can see that it sums transaction totals grouped by category within our date range in the Settings section. You can change this query to query whatever you want.
ARRAYFORMULAs in cells
J2:L2 lookup the hide-status, type, and group of the categories returned by the query.
When we pass these results into the next section, we will use columns
J:L to filter (i.e. out hidden categories) and sort (i.e. by type and group) the data. Personally, I think this— aggregate all categories, lookup category metadata, apply filters— is a more performant way of taking these steps than trying to enrich the transaction query with lookups on every row (to run this operation in one fell swoop).
In the next section, in
S2 we filter and sort the data. We are just filtering out hidden categories but you could also filter on expenses-only or specific groups. Have fun with it!
Two fussy arrayformulas in
R3 scan the data in
S:V and assign row indices for types, groups, and categories. You can see that row zero will have a type, row one will have a group, and row 2 will have a category, etc…
Both formulas define the variable
MORE_SPACE and link that to the More Space setting to determine how much spacing to apply. Again, instead of linking to a toggle, you can hardcode this to suit your purposes.
Now that we know which rows all the information will be on, we can render the data.
The formula in
B3 creates a
SEQUENCE of indices that encompasses all type, group and category rows in the hierarchy by getting the
MAX value in column
S. Next, it uses the
MAP function to map each row index into the name of a type, group or category. It does this through the use of nested
IFERROR statements, essentially checking if each row index exists within first the category index column, then the group index column, then (finally) the type index column. If there is a match, an
XLOOKUP function will fetch the text from the appropriate column
In a similar way, the formula in
C3 creates a sequence of row indices and looks for matches in the index columns to determine what type of row each one is. In this example, if it is a category row, the category total is fetched with an
XLOOKUP. If it is a group row, the formula dynamically creates a group total using
Hopefully, you see the basic mechanics of building row indices in
P:R and then rendering content based on row index matches. Of course, these formulas can be modified to include totals for category types, sparklines, etc…
A final touch to this example is the conditional formatting that helps to identify the type of content in each row.
For types, we are using the formula:
For groups, we are using the formula:
In a similar way to the row-index lookups, these formulas check for a match between the row index, which is derived from the distance from the start row
ROW($B3:$B)-ROW($B$3), and the values in the type index
$P$3:$P and group index