RangeSum Issues for a Reverse Accumulation

Tue, 21 May 2013 03:32:54 GMT

Hi Everyone,

I'm working on an inventory valuation historical trending chart, rolling 12 months, by warehouse. The current total values are known, and we're working backward with an accumulation of the transactions to see the trended warehouse totals for prior months.

Because I'm using a single total as the starting value, the standard QlikView Accumulation feature cannot be used. So, I've started with the total and added (because transactions out are negative) the RangeSum accumulation value for each period, as follows:

=Sum (TOTAL <Whse> (WarehouseQtyOnHand * WarehouseCurrentCostUSD) / StockPricePer)
+
rangesum (below(TOTAL
Sum ({$<Period = {"<=$(MONTHSTART(Date))"}>} StockTranQuantity * WarehouseCurrentCostUSD)
,0,12))

This works perfectly when a single warehouse is selected:

But, when I de-select the single warehouse filter, the line changes considerably (second line from the top, same red/pink color):

My guess is it's some strange way that RangeSum looks at the data, looking across the warehouses, instead of down each. I've tried various combinations of wrapping the Rangesum calculation in the AGGR function, by warehouse, hoping that this would keep Warehouse data streams separate, but it didn't appear to work.

Has anyone dealt with a similar issue? How did you overcome it?

Thanks in advance,

Re: RangeSum Issues for a Reverse Accumulation

aniketsr — Mon, 16 Jan 2017 09:42:23 GMT

Hi David,

I have the same kind of issue can you please guide me in case of rangesum when the value is negative it does not calculate properly.

topic RangeSum Issues for a Reverse Accumulation in QlikView

RangeSum Issues for a Reverse Accumulation

Re: RangeSum Issues for a Reverse Accumulation