Adding column totals

[David_B]

I have an app where I would like to record sales which I have already done.
I would also like to have a record of year to date totals from each vendor.
My record totals sales for each transaction. When I try to add the figures for a YTD total it doesn’t work.
I have a View where in the App Formula I put SUM(Sales[Total])
When I click Test it shows the total of the figures in the column Total
But, it does not show me a total in my App or put the figure in my Google Doc form.
The column type of each is price if that matters though I have changed both types to number and it still does not work.

[Aleksi]

Are you using normal column for that calculation? If you add that formula into virtual column, it should calculate the total dynamically.

[David_B]

David_B:

SUM(Sales[Total])

I have tried it both ways. It still does not show the latest sale in the YTD total.
Someone else suggested I use SUM(Sales[Total]) + [_THISROW].[Total]
But this also only shows YTD total from the previous sales not the latest sale entry.

[Steve]

Your problem description is confusing and incomplete.

David_B:

My record totals sales for each transaction.

How?

David_B:

I have a View where in the App Formula I put SUM(Sales[Total])

Views don’t have app formulas. Please clarify.

David_B:

But, it does not show me a total in my App or put the figure in my Google Doc form.

What do you mean by “Google Doc form”?

[David_B]

In the app or the Google doc. When I wipe out all of the test sales I have on the Google doc and enter a new sale it shows that sale but for YTD it shows 0. Then the next sale I enter it shows the previous sale but doesn’t add the current sale to the total.
At the moment I have a virtual column YTD.
In the App Formula is where I put SUM(Sales[Total]) + [_THISROW].[Total]

[Steve]

Please post screenshots of the following:

1. The worksheet, specifically including the header row.
2. The table’s column list from Data >> Columns in the app editor.
3. The column’s configuration screen.
4. The App Formula expression displayed in Expression Assistant.

[David_B]

I think these are what you requested. You can see in the worksheet how it only records up to the current sale.

[Steve]

David_B:

But, it does not show me a total in my App or put the figure in my Google Doc form.

Please clarify what this means? From your screenshots, it appears the computed YTD is in fact being written to the spreadsheet.

[David_B]

Yes it writes to the spreadsheet.
But if you look at the Total column and the YTD column you can see that adding the Total column comes out to $40.00. The YTD column only shows $32.00
Granted I could add the latest sale in my head to get the total but I hope that in the future these figures will be much larger.

[Steve]

Consider your expression:

SUM(Sales[Total])

This sums all existing values in the Total column of the Sales table. While in the process of adding a new row, the new row doesn’t yet exist in the table, and won’t exist until saved. Consequently, the SUM() expression won’t include the Total column value of the row being added. You will, in fact, have to explicitly add the new row’s total to the sum.

I would also encourage you to consider using Initial value rather than App formula. Initial value is only computed when the row is first added; App formula is recomputed every time the row is modified.

[David_B]

OK, I can live with that. I just thought there may be a way to include the current sale.
I did change the formula to the Initial Value as you suggested.
Thanks Steve,
David