topic Cumulative Sum (Rangesum) by week starts over on first day of month in App Development

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

mikegrattan — Thu, 02 Feb 2023 16:38:22 GMT

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.

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

edwin — Thu, 02 Feb 2023 21:39:52 GMT

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

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

edwin — Thu, 02 Feb 2023 21:47:12 GMT

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:

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

mikegrattan — Thu, 02 Feb 2023 23:06:10 GMT

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

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

edwin — Fri, 03 Feb 2023 13:33:01 GMT

have you tried adding your set analysis into the AGGR?

you may have to share some sample data

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

mikegrattan — Fri, 03 Feb 2023 15:35:46 GMT

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.

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

mikegrattan — Fri, 03 Feb 2023 20:36:10 GMT

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

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

Now the following expression works: