Cumulative Sum (Rangesum) by week starts over on first day of month

[mikegrattan]

I have the following expression in a table:

aggr(rangesum(above(sum({<DateFlag_CurrentWeek={1}>}ShippedQuantity),0,RowNo())),CommodityCode,ShipDate)

It kind of works, but something is wrong.  I would like to have a cumulative sum of ShippedQuantity by day, which starts over every Sunday, even when there's a new month included in the week's data.

Edit:  added a different screen shot, that includes the ShippedQuantity measure for comparison.

[edwin]

try this:
Aggr(RangeSum(Above( (　sum(WeekMeasure)　) , 0, RowNo())), (StartOfWeek, (Numeric, Ascending), (Text, Ascending)), (WeekDate, (Numeric, Ascending), (Text, Ascending)))

you need to have the week and the date in your table.  the actual sum is also shown and you can see when it resets.  the table needs to be sorted by week and date

[edwin · Accepted solution]

i think what you are missing is just the dimension, all your aggr dimensions should be added to your table.  here i have a 3rd dimension to aggr by:

[mikegrattan]

I got the following formula to mostly work.  It aggregates the Rangesum just fine, but it's ignoring the set expression for my DateFlag_LastWeek.  I tried adding the set parameter in other parts of the expression but still can't get it to work.  Any ideas?

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

[edwin]

have you tried adding your set analysis into the AGGR?

you may have to share some sample data

[mikegrattan]

I am getting correct cumulative numbers, but only when I set a filter manually.

The set analysis isn't restricting the data set as I would expect.  In the example below, I added BusinessArea, CommodityCode, and DateFlag_LastWeek to the set analysis and inserted it in both the aggr and the Sum functions:

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

I don't get any numbers until I set the filters as Selections.

[mikegrattan]

This does work; I found more information in another post:

Uncheck 'Include Zero Values' under Add-ons -> Data handling

Now the following expression works:

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