Problem with RangeSum and set expression

[mikegrattan]

Please help me figure out the set expression below.

I have the following expression which is working, as long as I am manually selecting a CommodityCode and a DateFlag. It does not work by itself at this point, even though I am specifying the correct filters in the set expression.

What I would like to do is modify the expression so that it will work for a specific CommodityCode, such as LTC, and a specific DateFlag, such as DateFlag_SixWeeks.  The DateFlag_SixWeeks dimension is defined in the data load, and it selects all dates that are prior to the current week - 6 weeks; the value is 1 when true and 0 when false.

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

The table I'm using for this example contains the following columns:

CommodityCode
Week
Avg (cumulative expression above)

Although ShipDate is not in the table it appears that it is needed in order for the correct accumulation week-over-week.

I imagine that the set expression, {<DateFlag_SixWeek={1},CommodityCode={'LTC'}>}, needs to be added to the RangeSum and/or Aggr functions but I have not been able to get it to work after trying several variations.

Any suggestions?

[mikegrattan]

I'm attaching a QVF file with limited data in case anyone wants to take a look at what I've got so far.  As you can see, if you set the DateFlag_SixWeek to '1' and the CommodityCode to 'LTC', the data looks fine.  And, although I've got the set expression set the same way, removing the filters from the filter pane results in incorrect results.

Hopefully someone can help with this!  Thanks!

[mikegrattan]

@sunny_talwar ...do you have time to take a look?  

[Kushal_Chawda]

@mikegrattan  without filter you just want to show LTC?

[mikegrattan]

Hi Kushal.

Without having to set filters manually, I want to show  {<DateFlag_SixWeek={1},CommodityCode={'LTC'}>}.  I need the set expression to be part of the overall expression because I want to set up charts that are specific to the last six weeks and for specific commodities.

Example:

[Sabrina_V]

I try something please check the column column AVG TEST. However I do not understant why your number changed for LTC when you pick the value

[mikegrattan]

Hi Sabrina.

Using the IF statement around the RangeSum is an interesting approach and seems like it should work in theory, but for some reason it's not excluding the other commodity codes in the table (not filtering on 'LTC').  Also, like you said, it looks like the numbers are not cumulative from the oldest week to the newest week and they appear to be incorrect as well.  

I appreciate the contribution!

[Sabrina_V]

Two ideas : 

Try to put on the Aggr function, the same fields existing on the set analysis.

Maybe you can try the if with an aggr ?

[mikegrattan]

I'm not sure how to go about doing that...I've tried adding another aggr around the whole expression but it's saying it requires two arguments.  Sorry, but I get confused with aggr and how it's supposed to work.

[Kushal_Chawda · Accepted solution]

@mikegrattan  try below expression, I have used set for two Commodity Codes. You can update your set according to your need to include as many Codes you want

sum({<DateFlag_SixWeek={1},CommodityCode={'LTC','ARL'}>}
aggr(rangesum(above(total avg(total <CommodityCode,Week>
aggr(Sum({<DateFlag_SixWeek={1},CommodityCode={'LTC','ARL'}>} EquivalentQuantity),CommodityCode,ShipDate_DayOfWeek,Week)),0,6)), CommodityCode, (Week,(NUMERIC,ASCENDING))))