I use pivots extensively in Excel/LibreOffice/Google sheets and find that the lack of a native solution in Grist hampers my adoption across a number of projects.
The community widget using pivottable.js created by jperon is ‘okay’ but suffers from the limitations of the underlying library (sub-totals being the biggest issue imho) plus the styling kinda jars.
Is there any movement towards getting a native pivot table widget?
Happy to talk directly and/or help in development and testing if something is in the works.
I’m not an expert pivot table user, so forgive my naïveté. Is there something that pivot tables can do that Summary Tables can’t?
In my experience, the only difference is the formatting, with all records as rows in Summary Tables, rather than having some breakdown categories as columns as in pivot tables.
I admit there could be some work on the aesthetics of the community widget. Plus some minor quirks here and there. It’s open source isn´t it? I guess we could just improve it on a fork?
The Javascript library that the widget is based on has been around for a long time and hasn’t changed much in that period.
There are other issues besides the look and feel with the library… sub-totalling on category being the most obvious.
IMHO, if Grist really does want to be a viable Excel replacement then having native pivot table functionality really needs to be addressed as a high priority.
Put me down as another strong vote for this. Pivot tables are a pretty important feature in Excel and as soon as someone realizes that doing a month by month budget spreadsheet requires typing twelve multi-line SUMIF statements in Grist’s tiny-text formula editor without making any typos, some of Grist’s shine starts to fade. (At least, it has for me.)
I tried the Custom pivot table widget with no results - the table view was just blank. I don’t know what I’m doing with it so I suspect I could get it to work with some experimenting, but this is something that needs to be built-in and easy.
All I personally really need is the ability to specify the fields to combine into columns, along with the fields to create rows for. For example, in a general journal table, with date, account, and amount fields (and calculated fields for month and year), I want to be able to
Group in rows by Profit Center, then Account;
Group in columns by Year, then Month (So cols are labeled 2024 Jan, 2024 Feb, etc.), (and/or group by just Month and filter by Year).
Ideally, the summary table field selector would provide these options, allowing us to select which fields to use for the Group Rows, which for the Group Cols, and which to use for the data column or columns, along with their order.
This feature idea has been discussed here since at least early 2022, so I’m sure someone has looked into what would be involved. Is this significantly difficult? Is the Grist team open to letting us users sponsor features, and if so, any idea how much money we’d have to raise to get this implemented?
I can’t speak to the difficulty of development, but was this custom widget the one you tried? I’ve found it quite solid and it has a styling toggle to make it look more like a Grist table. A blank table view when using a custom widget would lead me to think it might be related to access level.
As for the SUMIF woes, I wonder if this personal budget template might help show how monthly summaries can be done using summary tables?
No, the one I tried was the “(jperon)” version. I’ll try this one next.
As for the monthly summary part shown in the personal budget template, the drawback to that method is that for a typical business’ monthly account summary, there are going to be more accounts than months, so the common way to show that is with accounts in rows, and time periods in columns. For summary data (e.g. budget and actual income and expense totals), time periods in rows can work, but it’s often the wrong “shape” for the data, especially if you’re comparing just two time periods with differences - that’s just three rows, but with possibly dozens of columns.
I suspect I’m not making any points that you and the Grist team aren’t already aware of, and you’re being helpful by providing an alternative approach that Grist can easily do as-is, so I do appreciate that. But I do want to convince the Grist team that this is a missing feature that could easily turn a potential customer back into an Excel user. There are a few other really useful/necessary features that are also in this category (e.g. table level document-wide trigger formulas on inserts, deletes and updates, and the ability to turn fields into “macro” buttons), but this one is needed by the spreadsheet guys too, not just database geeks like me).
Thanks for adding your voice to this - like you, I find that not having a built in pivot table is a serious drawback to me adopting Grist for more projects.
As I said in the original post, Excel, LibreOffice and Google sheets all have it.