Cumulative sum by month and type

[Sa_Rah]

Hi

Back with this formula again:

=aggr(  rangesum(  above(  Sum( distinct {<Table = {"SIP"}, [Course Title] = {"ImpP*"},Date=">$(=today())"}>}CTMAP)     ,0,rowno())  ),MonthYearDate  )

This is summing the distinct CTMAP values in each month, and providing cumulative total. This works fine. 

I now need to factor in a new field, BLMAP. BLMAP has two instances per month, each with the same value of CTMAP. This means that the above formula will ignore one instance of BLMAP due to distinct CTMAP, so I need to amend the formula to take both BLMAP instances into account. So currently say a monthly CTMAP value of 45 is being added to the cumulative sum, but I need a CTMAP value of 45*2 = 90 to be added instead. 

I've tried adding in aggr(,BLMAP) into the above function in front of the current Sum, above, rangesum and aggr but none of these seem to calculate correctly.

I've also tried adding in BLMAP to the overall aggr ie. aggr( , MonthYearDate, BLMAP) and  aggr( , BLMAP, MonthYearDate ) but these are just calculating one single value of CTMAP across the whole time period. 

Can someone help?

Thanks

Sarah

[MayilVahanan]

Hi

Try like below

=Sum(aggr(  rangesum(  above(  Sum( distinct {<Table = {"SIP"}, [Course Title] = {"ImpP*"},Date=">$(=today())"}>}CTMAP)     ,0,rowno())  ),MonthYearDate,  BLMAP)

[Sa_Rah]

Thanks! It doesn't quite seem to be working, should there be an additional bracket somewhere? 

Also I should probably add that BLMAP is a text field.

Really appreciate the help!

[MayilVahanan]

Oops, missed the closing bracket

=Sum(aggr(  rangesum(  above(  Sum( distinct {<Table = {"SIP"}, [Course Title] = {"ImpP*"},Date=">$(=today())"}>}CTMAP)     ,0,rowno())  ),MonthYearDate,  BLMAP))

It will create the temporary table based on MonthYear & BLMAP field with sum(CTMAP) expression and then do the summation of the end result.

[Sa_Rah · Accepted solution]

Thanks - I tried this formula and I think it did exactly as you described, calculated both for month and for cohort and then added together. It seemed to ignore the range sum element however and just calculated the above for the months individually.

However in playing around with this I chanced upon a solution (change highlighted in bold below),

=aggr(  rangesum(  above(  Sum( aggr(distinct {<Table = {"SIP"}, [Course Title] = {"ImpP*"},Date=">$(=today())"}>} Min(CTMAP),BLMAP)   )   ,0,rowno())  ),MonthYearDate  )