Is it possible to group an aggregate function by aggr calculated dimension

[Anonymous]

Hello,

I am trying to have a cumulative total of SKU issues per manufacturing year in a pivot table where "Year" followed by "Min_Lag" are my dimensions. So far I got it to work with the first formula(see below), however I am curious to know if I can replace the last argument "Min_Lag" with an aggregate function that acts just like my Min_Lag column in my data set. Formula 1 uses a table which is added to the script, I am trying to avoid this so I don't double the size of my data.

1) sum( aggr( rangesum( above( count(DISTINCT[SKU]),0,12) ),[Year], Min_Lag))

2) sum( aggr( rangesum( above( count(DISTINCT[SKU]),0,12) ),[Year], aggr(min(Lag),[SKU],[Year]))

Can Min_Lag be replaced with aggr(min(Lag),[SKU],[Year])?

Thanks

[sunny_talwar]

It is difficult to know without a sample... but try this

RangeSum(Before(Sum(Aggr(RangeSum(Above(Count(DISTINCT [SKU]), 0, 6)), SKU, [YEAR]), 0, ColumnNo()))

[sunny_talwar]

Or how about this may be

RangeSum(Before(Sum(Aggr(Count(DISTINCT SKU), YEAR, SKU)), 0, 6))

[Anonymous]

The second option was nice solution! I swapped the 6 in the last argument with the LAG dimension since it can definitely vary.

Thank you Sunny Talwar for your time an effort

[sunny_talwar]

No problem, I am glad we were able to help.