RangeSum cumulative total does not match actual data

[mikegrattan]

I'm working with the following table:

The goal is to get the cumulative total number  to match the sum of the actual Qty Shipped column.  Is that possible?  Here is the expression for the cumulative column:

Aggr(RangeSum(Above(sum(EquivalentQuantity),0,RowNo())),
(CommodityCode,(Numeric,Ascending),(Text(Ascending)),
(StartOfWeek), (Numeric, Ascending), (Text, Ascending)),
(ShipDate, (Numeric, Ascending), (Text, Ascending)))

[mikegrattan · Accepted solution]

Marcus,

It looks like my edited comment didn't get saved last night.  I tried with Total as well and got the same results.  However, I did discover that there's a Modifier option that I overlooked before.  Once I set that to Accumulation, I got the correct results but they are in reverse order. Note that the Modifier option created a new expression:

Aggr(RangeSum(Above(Total (　aggr(RangeSum(Above(sum(EquivalentQuantity),0,RowNo())),ShipDate,CommodityCode)　) , 0, 6)),
[$(=Replace(GetObjectField(1),']',']]'))], ([$(=Replace(GetObjectField(0),']',']]'))], (Numeric, Descending)))

I changed the sort to Ascending and got the correct results:

[marcus_sommer]

If it's worked now it's ok. but I think it should work without the aggr() stuff. At least in QlikView I wouldn't have needed it - with the default properties of the chart-calculation (I never change them). Maybe those properties are different and/or even extended in Sense ...

[mikegrattan]

I tried the "Accumulation" Modifier without Aggr and it did not come up with the correct results.  It looks like it needs the Aggr for whatever reason. Then the modifier creates an output expression which can be copy/pasted into the expression builder and you can set the Modifier back to None.