Cumulative total of weekly average

[mikegrattan]

I have a table with the following fields:

CommodityCode
Week
ShipDate_DayOfWeek
EquivalentQuantity
Avg

The expression for Weekly Avg is:
Aggr(Avg(TOTAL <Week> Aggr(Sum(EquivalentQuantity),ShipDate_DayOfWeek, Week)), ShipDate_DayOfWeek, Week)

This gives me a weekly average, which is repeated for each day of the week.  My goal is to get a cumulative sum of the weekly average.  For example; in the screen shot below the average for Week 06 is 21,149 and the average for Week 07 is 23,673, so the cumulative total for Week 07 would be 44,822.  Please advise; thanks for any help.

[Gabbar]

try this:-
Rangesum(Above(Aggr(Avg(TOTAL <Week> Aggr(Sum(EquivalentQuantity),ShipDate_DayOfWeek, Week)), (Week,(Numeric,Ascending))),0,rowno()))

[mikegrattan · Accepted solution]

Hi Gabbar,

That gives me some unexpected results.  I'm not getting every day of the week, or even the last day of the week.  I'm getting a combination of Tuesday, Thursday, and Friday, and the numbers are not cumulative.  

I played around with it some more and ended up with the following:

• Removed ShipDate_DayOfWeek from the table
• Used the Modifier feature to accumulate across all dimensions, starting with the expression I originally posted

The modifier output the following expression:

RangeSum(Above(Total (　RangeSum(Above( (　Aggr(Avg(TOTAL <Week> Aggr(Sum( EquivalentQuantity),
ShipDate_DayOfWeek, Week)),ShipDate_DayOfWeek, Week)　) , 0, 6))　) , 0, 6))

And now the table looks correct:

I'm going to try adding a set expression and see if it maintains its integrity; right now I'm manually filtering on LTC for the CommodityCode and DateFlag_LastSixWeeks = 1 for the time frame.  

Thanks for your initial response; if adding the set expression messes up my current results I'll post again and perhaps you can help point me in the right direction.

[mikegrattan]

Although I got the cumulative sum of the weekly average to work correctly using manually selected filters, I am now at a point where I'd like to get it to work with a set expression.  The following does not work, so I probably need to modify it so the set expression is processed at multiple levels within the overall expression.  If this needs a new question posted, I can do that.  

RangeSum(Above(Total (　RangeSum(Above( (　Aggr(Avg(TOTAL <Week> Aggr(Sum({<DateFlag_SixWeek={1},CommodityCode={'LTC'}>} EquivalentQuantity),
ShipDate_DayOfWeek, Week)),ShipDate_DayOfWeek, Week)　) , 0, 6))　) , 0, 6))