RangeSum Issues for a Reverse Accumulation

[#1]

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,

DJ

[aniketsr]

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.