1 Reply Latest reply: Jan 16, 2017 4:42 AM by Aniket Surve RSS

    RangeSum Issues for a Reverse Accumulation

    David Norton

      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:

      RangeSum - Single Warehouse.png

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

       

      RangeSum - Multiple Warehouses.png

      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