Hi Richard,

There's no easy, out-of-the-box solution for the above. Databases don't handle running totals very well (see e.g. here). What I'd do to get something like the above, is iterate over the results returned from the Aggregate (with a For Each), and calculate the running balance in the loop.

Thanks that is helpful.  

A few dumb questions 

1. Can the running total be an attribute added for a query or does it need to be part of the aggregate used?
2. If part of the query how do you pull up the previous running balance?  
3. I presume you use the For Each function to pull up the previous running total amount and then add it to the current value to get the current running total.  Is that correct?  
   
   

Hi Richard,

1. The easiest would be to add it to the Aggregate as an extra Attribute, but set it to 0 without it being calculated in the Aggregate itself. You can then use it later on.
2. I'm not sure I understand what you mean by "the previous running balance". You can't calculate the running balance in the Aggregate, you need to do that afterwards. However, you can of course store the running balance as part of the data in the database, in which case you can retrieve it. I would advise against that though, in general, as correcting accounting mistakes will force you to recalculate the running balance for all following records.
3. I would use the For Each to calculate the running balance by starting with the amount of the first record, then adjusting the running balance for each amount of subsequent records. Of course, this would entail getting all records ever, since you don't store the running balance anywhere. But you have that same problem in Excel.

Kilian,

Thanks for that.  It is very helpful.  When I said "previous running balance" I mean the running balance calculated for the previous record.  From my original example if I am calculating the running balance for 7/01/2018 I take the running balance for 5/01/2018 (1,220) and add that to the value for 7/01/2018 (400) to get 1,620.

Couple of questions:

1. In using the For Each calculation I presume that I save the running balance calculation in the running balance attribute.  Is that correct?  So for instance, the For Each calc is set up so that in the first record (1/01/2018 record) I save in the running balance attribute of 1,000.  For the second record and subsequent records, the amount save is Running Balance (n-1) + Value, where n is the current record number.
2. Does this work if I filter out records?  For instance if the number of records in an entity is say 200 but with filtering it is only 20, the running balance attribute is only updated for those 20 records?
3. How do I get the Running Balance for the previous record in the displayed list of records?

Hopefully these are not too basic questions.

Richard

Hi Richard,

I've attached a simple PoC that shows what I mean.

1. Yes, see the Preperation of the Web Screen.
2. It works for the records in the list. Check the logic and you'll understand why. There's no easy way to do that, but I guess that goes for Excel as well! You could perhaps use ListFilter after calculating the balance to filter out unwanted records, but in any case it'll be difficult to check for the user whether the running balance is correct without all data.
3. I'm not sure I understand what you mean.

Kilian,

That is exactly what I was after.  Thanks very much for your assistance.  Much appreciated.

Richard

You're most welcome :).