I would like to sum a quantity over a running 12 month period

I have a simple spreadsheet that i record my fuel usage on, the last column totals the fuel quantity over a running 12 month period. If uses SUMIFS in Google sheets, but this formula gets overwritten as blank when using my app.
I’m not sure how to write an expression to do this simple task in AppSheet.
Advice much appreciated

Sum across the last 12 months prior to the current record’s [Date]:

SUM( SELECT(
  Table[Veg Oil Qty] ,
  [Date] >= EOMONTH( [_THISROW].[Date] , -13) + 1
) )
4 Likes

Hi, thanks, it says ‘Can’t find Table’
If i assume that is a variable that needs changing to my sheet name it still doesn’t work (‘Sheet1’). Have i misunderstood?

Yes you need to change “Table” to the name of your Table in the app (which could be the same as the Sheet name).

1 Like

What syntax error am i making here?

What is the name of your Table inside of the app?

1 Like

Aha, thank you.
That appears to be working, strangely it has now changed the appearance on entries in the app, the ‘title’ of each entry is now that running total instead of the date as before

(i’m not sure why the creator view of the app only shows one entry, the app on my phone correctly shows all 3 entries, but the title issue remains)

Hi Marc, i notice that the running total shown in any given row pertains to the quantity figures up to the previous row.
As shown here, the first three rows still have my original formula in the cells, whereas the fourth entry is the only one after i implemented your expression.

Is there a way for the app to live calculate that total for the number just entered and show it in the total box (and thus enter it into the appropriate cell in the source?

You mean the sum just isn’t including the current record?

yes

I’ll give it a shot. I think that this is very close to what you want:

image

Several things I did differently:

  1. The SUM(SELECT()) statement only defined the start date, not the end date. I changed it. See it below.
  2. For debugging purposes I added two virtual columns, “Start” and “End”, to help to understand the period used by the SUM(SELECT()). Please note that I researched “12-month rolling period”. I could not find a satisfactory definition, so I took the liberty of defining it my way. Change it to match your requirements.
  3. I did NOT include the formula in the spreadsheet, only in AppSheet as a virtual column.

Here is how I defined the columns:

Start: EOMONTH( [_THISROW].[Date] , -13) + 1
End: [_THISROW].[Date]

Total:
SUM( SELECT(
Table[Veg Oil Qty] ,
AND(([Date] >= EOMONTH( [_THISROW].[Date] , -13) + 1), ([Date] <= [_THISROW].[Date]))
) )

Hope this helps.

Brian

1 Like

I believe the problem is that the expression is running when you are creating the new record. In such a case, the new record doesn’t yet exists in the Table, so the SELECT expression doesn’t pick it up.

You could add a term to the expression:

+
[Veg Oil Qty]

…in order to add the current record’s value to the previous SUM. The problem with this is if you ever edit that record again, the SUM will then be greater than it should be by that record’s amount. Will you ever be editing the record later?

1 Like

Thankyou for this.
Now that i’m aware of expressions in AppSheet i do not require the formula in the spreadsheet anyway :slightly_smiling_face:

1 Like

Thanks Marc, i will give this a test. No, realistically i will not need to edit a record later, once the oil has gone in the tank and been burnt there’s no going back (unless i’ve made a mistake!) :grin:

1 Like

Thanks again, this works a treat

1 Like