I feel like I’m missing something obvious. I have a super basic sheet that shows bank transactions (name, date, amount). In the Glide app, I’d like to be able to show ONLY ones where the transaction date is within the current month. I’m using Zapier to populate the sheet, so I can create whatever columns and formulas I need. I just need to see nothing but the transactions whose date is within the current month.
BONUS: can I show a chart that calculates the total of those transactions vs a budgeted amount. So if there were 3 transactions that totaled $150 and the budget is $250, I’d like to see a pie chart that shows how much has been used.
DOUBLE BONUS: if I could also have a tab that showed last month’s transactions and pie chart that would be fantastic.
Thanks!
Within current month, I am assuming you mean February, rather than the last 30 days.
If the current month you can use a Math column to extract the month from your item date. Month(date) where date is date column.
A couple of ways to filter the information. You can have a Today field, extract the Month from it, and filter via that. Or have a dropdown to choose the month to view.
I am waiting for a zoom meeting so made this. It’s a bit dirty regards where things write to, but it should explain things better than I can type them.
just added a graph, as I haven’t ever played with them before. Pie chart won’t work at the mo as the data isn’t set up right, but there is some data there to faff around with.
Zoom meeting is about to kick off, good luck.
1 Like
If you have a timestamp column already, you can create something like this:
-
Math column to extract the month from the timestamp: MONTH(T) with T being the timestamp.
-
Math column to extract the year from the timestamp: YEAR(T)
-
Template column to join the two columns above (will be something like 2-2021).
-
Math column to extract the month from the current time: MONTH(N) with N being the “Now” value.
-
Same process with year and template column.
If template column for “Now” is template column for Timestamp then that row is for the current month.
You can follow the same process for “last month”.
1 Like
Another choice for you is:
YEAR(T) * 100 + MONTH(T) so you have a unique number like 202101.
YEAR(N) * 100 + MONTH(N)
If the two columns are equal then the row is for the current month.
For last month, it would be YEAR(N)*100 + MONTH(N)-1
1 Like
Thanks @Mishta_P and @ThinhDinh for the responses! I’ll have to play around some more, I guess. Sure would be nice if it was simpler to implement, using the built in filtering options.
Like “if [date of transaction] is within [current month/week/year/quarter]” and blam, the right results.
It’s not a huge amount worse than that. Just realised I didn’t make the app copyable. I’ll do that shortly, so you can see the back end.
1 Like
Ahhhhhh, that’s super helpful, to see how all the pieces fit. Thanks so much!
I think the last piece I’m not sure about is the “settingsish” table. What does that one do?
That is just to work some of the data in the background, such as the choice menu.
I would normally do this in different parts of the data, but for this example it was easier just to dump it in one place.
Gotcha! I’m working my way through it. Thanks so much! I’m quite familiar with Airtable, and how everything connects there. Not so used to having Google Sheets and also Glide-specific columns, so working in two different places. If Glide makes a way to populate their own tables using Zapier, I’d gladly do it that way instead of using Google Sheets 
2 Likes
Glad my wait for the zoom meeting was helpful. Good luck!