I would like to sum a quantity over a running 12 m...

greenbeast · 07-27-2021 12:33 PM

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

Marc_Dillon

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
) )

greenbeast

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?

Marc_Dillon

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

greenbeast

What syntax error am i making here?

Marc_Dillon

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

greenbeast

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)

greenbeast

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?

Marc_Dillon

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

greenbeast

yes

Marc_Dillon

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?

greenbeast

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!)

greenbeast

Thanks again, this works a treat

Brian_Morgan

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

Several things I did differently:

The SUM(SELECT()) statement only defined the start date, not the end date. I changed it. See it below.
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.
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

greenbeast

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

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