topic Calculating a running total including period not shown in the table in App Development

Calculating a running total including period not shown in the table

JurgenW — Sun, 14 Jul 2024 08:11:38 GMT

Hello all

I'm trying to calculate a running total in a table including the data from the period that is not shown/selected.

Suppose I have the following data

Month	Profit
202309	500
202310	400
202311	-100
202312	800
202401	-150
202402	200
202403	400
202404	-100
202405	200
202406	200

Now I want to display a running total of the profit in a table that starts with 202401 (or whatever the user selects) but it should include the profits/losses from 2023. So I would like the output to be.

Month	Profit	Balance
202401	-150	1450
202402	200	1650
202403	400	2050
202404	-100	1950
202405	200	2150
202406	200	2350

I get the correct result with '=RangeSum(Above(({<Month=>}Sum(Profit)), 0, RowNo()))'

But then my table also shows the months that were not selected. So how can I tell QS not to show these months? ('Include zero values' makes no difference as the values are non-zero)

Or is there another way to write this formula so that QS returns a single value in stead of a table with results per month?

Any help is greatly appreciated.

Re: Calculating a running total including period not shown in the table

brunobertels — Mon, 15 Jul 2024 09:23:45 GMT

for Month Dim use this

=aggr(if(Month>='202401',Month),Month)

Then as mesure :

aggr(RangeSum(Above(({1<Month=>}Sum( Profit)), 0, RowNo())),Month)

you should have this

hope it helps

Re: Calculating a running total including period not shown in the table

JurgenW — Mon, 15 Jul 2024 12:21:29 GMT

Hello Bruno

Thank you, it works. (FYI: No need to change the dimension to a formula. It works anyway and it keeps the flexibility of the user selecting which period he wants to see).