I’m using Grist for the first time to look at some of my own bank transactions, and I’m trying to figure out how to create a summary table of a filtered view, so I can see a summary of all the things I’ve spent money on in the last month or so.
Where I am so far
If it helps, here’s a sample of the data that I’ve exported from my bank (if it helps they’re n26, and they provide a CSV export all the day back to when I joined in the mid 2010’s):
I know I can create a filtered view, to only show entries from the last 30 days, but creating a summary table creates the summary from the entire table.
A possible approach, and where I get stuck
I’ve also read there that I can create an extra boolean column and group then filter by the boolean, as described here by @alexmojaki:
I guess could try this boolean column trick, but it seems like I’d need to hardcode the cut-off date I’m filtering by to group by the boolean formula - are there any magic variables, that let me refer to the filter I have already set, like __MATCHES__FILTER_A__ or something to that effect?
This would allow me to see the summary view update, based on the range specified on the date filter in the source table - allowing me to quickly see transactions grouped by payee, in the last week, last 30 days, last 90 days and so on - and I can imagine others wanting to be able to see filtered, grouped views of a single table in other scenarios then me just fiddling around my own finances.
There’s a hidden column called group that contains all the grouped records. Would creating individual formula columns that compute the amount for the last 7/30/90 etc. days work for your use case?
It’s still a workaround along the lines of Alex’s suggestions, so it’s not quite ideal. Being able to filter records before summarizing is really what we need here, and it seems like a natural thing to have (and would nicely mirror SQL).
There’s no way to refer to filters from formulas, unfortunately.
To add to @georgegevoian’s answer, in Grist formulas work on the data level, but filters are a property of the view. E.g. you can have different pages displaying the same data with different filters, and if the document is shared, different users can see the same page with different filters. That’s the reason why formulas can’t refer to filters.
However, what you are looking for can be accomplished with just formulas. Here is an example:
This is based on the “Credit Card Activity Template” in our template gallery, but I added a new page called “Recent Activity”. It has a section on top to define the range of dates:
You enter the number of days into the yellow cell. (I added a manually set “Current Date” just because the sample data is from 2019, but you can adjust to make it the TODAY() formula.)
Based on Num Days, I calculate Start Date of Selection, and then the actual transaction data has a new column Selected? with this formula:
The result is that only the records after Start Date of Selection have Selected? set to True.
Then there is a summary table grouped by Selected? column (for total of selected records), and one grouped by Selected? and Category (for category subtotals). Both the summary widgets and the original data have a saved filter to only show selected records. (You can hide the “Selected?” column, I only left it visible for clarity of what’s going on.)
As you adjust the number of days in the yellow box, you get to see just the records in that date window, their total, and their summary grouped by category.
