Hi,
I’m wondering if there’s any way to create a Summary table in Grist that would use a field in the source table to generate dynamic columns in the summary table. This would be similar to Excel pivot tables or MS Access crosstab queries, where one can specify a field to use for the column headings.
My use case is a table with lots of ratings for different products, with each rating having a type, user, date, and location associated with it. In my table, I would have a formula column concatenating the rating type, location, and user fields, and then would want to use that formula field for my dynamic column in the summary table, so that I can summarize the ratings, grouping on product, but displaying these ratings across columns showing each type/location/user combination.
Thanks!
Hi Michael,
Welcome to the forum!
Happy to help. It sounds like you want a cross tabulation table. While that’s currently not supported in Grist, there’s a few ways to group and display data in this way. There’s a thread about it here with multiple methods: Is there a cross-tabulation table?
For your use case in particular, I tried building an example using the linked summaries table method (listed in the linked thread above) because it requires the least amount of fiddling with formulas and will update more seamlessly as you add more types, users, and locations. You can see the example my example here: Summarizing with Linked Summary Tables - Grist
The key feature is the ability to create a summary table and link it to an existing summary table that shares at least one group by column.
In the screenshot below, I am creating a second summary table, grouping by Product and CONCATENATE, and linking it to an existing summary table on the page that groups by PRODUCT.
Because the columns I am concatenating all have the type “Text”, the concatenate formula I used combined strings: $Type + "-" + $Location + "-" + $User. If you need help concatenating, let me know!
Thanks so much for the help, Anais!
1 Like