Hello Gliders, I’m having trouble setting up charts on data. It’s for a personal finance app for me and I’m syncing bank transactions via a Google Sheets extension.
I want to show account balances on multiple dates to show the progress of those accounts. Source for the chart is a query column of my different account balances.
The problem I’m facing is that every time the Google sheets extension syncs it creates a new row with the current account balance. This results in sometimes having multiple balances for the same date.
When showing a line chart the balances with the same dates sum up and I end up with false numbers on dates that have more than one sync (row).
I tried filtering my query column but couldn’t figure out how to keep only the last synced row for each date.
Thank you for your help!
Hi, Mikaeldorval!
I think you can use rollup to finde Latest date then you can use it in you query to check the date of transaction is Latest date.
Yes, this should work. To clarify, in your data table:
- convert all dates to numbers (
year(date)*10^4+month(date)*10^2+day(date))
- create a relation column (matching multiple) that relates this value back to itself
- create a rollup column that points to this relation and grabs the maximum date value column from step 1
- create an if-then-else column that outputs
true when date value matches the rollup value
- use this
true value as a filter in your query (is checked)
1 Like
I tried your solution but the problem is since every sync gives a date only with a time of midnight for each one there is no max number for a day. It relates back every sync for each day.
Is there another way this could be done?
Instead of rollup, use a single value column that just returns the last record (assuming all rows are in chronological order).
1 Like