Compared with previous data?

I try to record my money spend record. and I want to see how many increase / decrease my money from the yester day

I know $A… $B is selcet column. and also I can use widget to see a chart whole data
but I wonder is there a way to see previous cell as a value in the script

It is possible but can be tricky. The basic idea is to have a column of type Reference, pointing to the previous record in the same table. E.g. if it’s called Previous, then you can calculate the cumulative values by having a column named Cumulative of type Numeric, with the formula $Previous.Cumulative + $Amount.

Constructing the “Previous” column is simplest if it can be done using lookups, e.g. by looking up the previous date or previous month using the current date or month. You can use date functions like DATEADD for those cases.

If a simple lookup doesn’t work, you may need to collect all records, sort them by some field (e.g. timestamp), find the current record, and find the previous one from that. A particular difficulty is how to do this efficiently, so that it still works acceptably when you have thousands of rows. One approach is described here How to create a formula to show accumulated amount per account - #6 by dmitry-grist.

There are some other threads with a similar question, if you search the forum for the word “previous”.

By the way, we are also hoping to create a simpler way to find previous records, and do it efficiently, but I don’t have an ETA on that yet.

1 Like