Six Week average as of the Ship Date

[mikegrattan]

I have the following expression that gives me a cumulative total of LTC EquivalentQuantity shipped for the current week:

Aggr({<DateFlag_CurrentWeek={1},CommodityCode={'LTC'},Flag_Shipped={1}>} RangeSum(Above(sum({<DateFlag_CurrentWeek={1},CommodityCode={'LTC'},Flag_Shipped={1}>} EquivalentQuantity),0,RowNo())),
(CommodityCode,(Numeric,Ascending),(Text(Ascending)),
(weekstart(ShipDate)), (Numeric, Ascending), (Text, Ascending)),
(ShipDate, (Numeric, Ascending), (Text, Ascending)))

And the Prior Wk expression is:

Aggr({<DateFlag_LastWeek={1},CommodityCode={'LTC'},Flag_Shipped={1}>} RangeSum(Above(sum({<DateFlag_LastWeek={1},CommodityCode={'LTC'},Flag_Shipped={1}>} EquivalentQuantity),0,RowNo())),
(CommodityCode,(Numeric,Ascending),(Text(Ascending)),
(weekstart(ShipDate)), (Numeric, Ascending), (Text, Ascending)),
(ShipDate, (Numeric, Ascending), (Text, Ascending)))

And here is what I have so far for the 6 week average:

Aggr(sum({<DateFlag_SixWeek={1},CommodityCode={'LTC'},Flag_Shipped={1}>} EquivalentQuantity),
CommodityCode) / 6

And here is what it looks like in a table:

In order for the 6 wk average to look right in a line chart, I would like the number to populate for every ShipDate shown.  Is there a way to do that?

Thanks.

[neerajthakur]

Aggr(Sum(Aggr(sum({<DateFlag_SixWeek={1},CommodityCode={'LTC'},Flag_Shipped={1}>} EquivalentQuantity),
CommodityCode) / 6),ShipDate)

Try This

[marcus_sommer · Accepted solution]

Such things could be quite tricky and complicated and have sometimes side-effects, for example displaying unwanted extra rows within the object ... Therefore it might be more expedient to transfer the essential parts into the script.

So ways to reach to show values for dimension-values to which they not native belong to are extending the aggr() with NODISTINCT statement and/or the aggregation with a TOTAL statement and/or using {1} within the set analysis to enforce the considering of all values - independent to the dimensions and/or the selection state. Sometimes it also requires to apply one or more extra aggr() wrappings.

Quite often it's not possible to avoid the above mentioned extra rows within this logic-layer. Helpful in such cases is then usually to apply another boolean check against it - for example something like this:

aggr(Expression, Dim) * if(Condition, 1, 0)

means mainly that at first a lot of rows are evaluated and afterwards only the wanted ones are picked. A side-effect would be a rather slow performance - at least compared against simple dimensions/expressions.

[mikegrattan]

The NODISTINCT worked for this scenario. Thanks Marcus.