Accumultation / running totals

[kangaroomac]

Good Day,

I'm having a head scratcher with regards to accumulation.

It is working when no selections on i.e. Year is made.

Year	Amount	Accumulation
2010	100	100
2011	200	300
2012	300	600
2013	400	1000
2014	500	1500
2015	600	2100
2016	700	2800

The goal is to have a selection made on Year (i.e. 2015 and 2016) and have it with the same accumulation / running total figures as above.

The Accumulation expression so far:

RangeSum(Above(Total

Sum(

     {<

     >}

         Amount)

, 0, RowNo(Total)))

Unfortunately doing the logic in the script is not an option (the real solution enables users to make various filter selections).

I have attached a sample QVF.

Example of required result:

Selection made: 2015, 2016

Desired result:

Year	Accumulation
2015	2100
2016	2800

[sunny_talwar · Accepted solution]

Try this:

RangeSum(Above(Total Sum({<Year>} Amount), 0, RowNo(Total))) * Avg(1)

[sunny_talwar]

In addition since Year is the only dimension, you might not even need a TOTAL qualifier here.

RangeSum(Above(Sum({<Year>} Amount), 0, RowNo())) * Avg(1)

[kangaroomac]

Hi Sunny,

Thanks, that did the trick.

Your additions are Year and * Avg(1).

I understand Year (same as Year = i.e. ignore any selections made on Year)

The * Avg(1) works, but why?

[sunny_talwar]

Adding Avg(1) would be 1 for all the years which are selected and for non-selected years Avg(1) will be 0. So this is an alternative of using an if statement which allow me to make those years 0 which are not selected.