topic Re: Please help me modify my Cumulative Sum by Week set expression so it will work with parameters in App Development

Problem with RangeSum and set expression

mikegrattan — Tue, 18 Apr 2023 17:37:41 GMT

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?

Re: Please help me modify my Cumulative Sum by Week set expression so it will work with parameters

mikegrattan — Wed, 12 Apr 2023 15:32:49 GMT

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!

Re: Problem with RangeSum and set expression

mikegrattan — Thu, 20 Apr 2023 20:37:42 GMT

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

Re: Problem with RangeSum and set expression

Kushal_Chawda — Thu, 20 Apr 2023 23:16:48 GMT

@mikegrattan without filter you just want to show LTC?

Re: Problem with RangeSum and set expression

mikegrattan — Fri, 21 Apr 2023 14:03:30 GMT

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:

Re: Problem with RangeSum and set expression

Sabrina_V — Fri, 21 Apr 2023 14:36:47 GMT

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

Re: Problem with RangeSum and set expression

mikegrattan — Fri, 21 Apr 2023 15:02:06 GMT

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!

Re: Problem with RangeSum and set expression

Sabrina_V — Fri, 21 Apr 2023 15:09:14 GMT

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 ?

Re: Problem with RangeSum and set expression

mikegrattan — Fri, 21 Apr 2023 16:14:10 GMT

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.

Re: Problem with RangeSum and set expression

Kushal_Chawda — Fri, 21 Apr 2023 20:28:52 GMT

@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))))

Re: Problem with RangeSum and set expression

mikegrattan — Fri, 21 Apr 2023 21:33:01 GMT

Wow...yes, that works perfectly. Thank you so much.