topic Re: RangeSum cumulative total does not match actual data in App Development

RangeSum cumulative total does not match actual data

mikegrattan — Thu, 16 Mar 2023 22:35:10 GMT

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

Re: RangeSum cumulative total does not match actual data

marcus_sommer — Mon, 20 Mar 2023 13:55:56 GMT

Did you try it without the aggr() stuff, just?

RangeSum(Above(sum(EquivalentQuantity),0,RowNo()))

Re: RangeSum cumulative total does not match actual data

mikegrattan — Mon, 20 Mar 2023 15:23:20 GMT

Hi Marcus,

Here are the results when I remove the aggr():

I'm filtering on one week. My understanding is that the aggr() is needed, along with the dimension sorting, in order to get a cumulative total by week. Thanks.

Re: RangeSum cumulative total does not match actual data

marcus_sommer — Mon, 20 Mar 2023 16:13:51 GMT

It depends in regard to the used dimensions within the chart and the default-ordering of the object. Regarding to your first screenshot it looked that a normal cumulation-calculation might be usable. Before looking further add a TOTAL to the above and see if it changed the results.

Re: RangeSum cumulative total does not match actual data

mikegrattan — Mon, 20 Mar 2023 16:50:53 GMT

The expression now looks like this:

RangeSum(Above(Total sum(EquivalentQuantity),0,RowNo()))

But the numbers look exactly the same as they did without the Total. I am currently filtering on a single week and a single CommodityCode; same as last scree shot.

Re: RangeSum cumulative total does not match actual data

marcus_sommer — Mon, 20 Mar 2023 17:24:54 GMT

The expression sum(EquivalentQuantity) is the same as QTY Shipped or is it different?

Re: RangeSum cumulative total does not match actual data

mikegrattan — Mon, 20 Mar 2023 17:31:13 GMT

Yes, I'm just using a different label for the table column.

Re: RangeSum cumulative total does not match actual data

marcus_sommer — Tue, 21 Mar 2023 06:56:49 GMT

I wouldn't expect such behaviour by a "classical" data-structure - in data-model + object-design. Therefore I suggest to re-create the object from the scratch and step by step. At first with one dimension and the normal sum() + rangesum(above(sum())) without any further measures in regard to sorting, coloring and so on (regardless if fixed set or calculated) or alternate states and similar stuff.

It will exclude any possible corruption of the object and showing which measure leads to the seen behaviour. Just a guess - the cause may a calculated dimension ....

Re: RangeSum cumulative total does not match actual data

mikegrattan — Tue, 21 Mar 2023 22:54:48 GMT

I started over and it still looks like it won't accumulate correctly. Just using the basics; I selected one commodity, LTC, and one week of data. There are no calculated dimensions. I changed the label for Shipped Qty so it reflects the name of the measure more precisely, so now it's labeled Equiv Qty. I tried with and without Total in the expression but I'm getting the same results:

Cumulative: RangeSum(Above(sum(EquivalentQuantity),0,RowNo()))

Re: RangeSum cumulative total does not match actual data

marcus_sommer — Wed, 22 Mar 2023 09:24:51 GMT

Please try it again with: RangeSum(Above(TOTAL sum(EquivalentQuantity),0,RowNo()))

Re: RangeSum cumulative total does not match actual data

mikegrattan — Wed, 22 Mar 2023 15:24:55 GMT

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:

Re: RangeSum cumulative total does not match actual data

marcus_sommer — Wed, 22 Mar 2023 15:57:59 GMT

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

Re: RangeSum cumulative total does not match actual data

mikegrattan — Wed, 22 Mar 2023 16:47:57 GMT

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.