Crosstab with dynamic columns

Two options. One creating a custom widget. In fact, @jperon has made one that you can use: PivotTable custom widget

I should mention also that in Grist, it’s often sufficient to create a page with linked widgets instead, since it allows getting at the same data conveniently, in a row-oriented format. But there are reasons to prefer a cross-tab / matrix format, of course.

For a native way in Grist, there isn’t a great way yet – but you can do it if you are willing to create a separate column for each month separately and manually (rather than dynamically).

That could work fine as long as the number of columns is small. If it gets large, in addition to the headache to create them, it would also cause bad performance. For a small number of columns, here is an example how to achieve this:

https://public.getgrist.com/c36Qs7mnP2jz/Credit-Card-Activity-Cross-tab/p/6

Here I have a summary table Activity [by Category], and a hidden summary table Activity [by Category, Month]. The first one looks up values from the second in each of the month columns, using formulas like this:

month = "2019-01"
Activity_summary_Category_Month.lookupOne(Category=$Category, Month=month).Amount

Unfortunately, the month columns have to be created manually, and the formula in each needs to be updated manually to have the month match the name of the column.

There is a similar example here: How to do traditional pivot table in grist - #2 by natalie-grist

We’ve spent some time at one point trying to come up with a good feature to do this nicely. One thing that makes it harder is that there are similar-looking situations where people want to enter data in this layout too (e.g. creating a budget, for an example similar to this data). We don’t have any great proposals that address these wishes yet.

1 Like