Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Contributor II

## Calculating a running total including period not shown in the table

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.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Master

Hi

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

2 Replies
Master

Hi

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

Contributor II
Author

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