topic Re: Cumulative sum by month and type in App Development

Cumulative sum by month and type

Sa_Rah — Tue, 12 Sep 2023 09:09:17 GMT

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

Re: Cumulative sum by month and type

MayilVahanan — Tue, 12 Sep 2023 09:45:54 GMT

Try like below

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

Re: Cumulative sum by month and type

Sa_Rah — Tue, 12 Sep 2023 09:59:11 GMT

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!

Re: Cumulative sum by month and type

MayilVahanan — Tue, 12 Sep 2023 10:01:22 GMT

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.

Re: Cumulative sum by month and type

Sa_Rah — Tue, 12 Sep 2023 14:15:24 GMT

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 )