2 Replies Latest reply: Jul 1, 2011 2:28 AM by Artjoms Tukums RSS

    Aggregation in Stock Aging

    Artjoms Tukums

      Hi everybody!

       

      I'm calling for help, 'cause my patience gave up.

       

      Situation description: Dynamically calculate Stock Aging by periods using Stock and Purchase data. This can be done with FIFO method. We can fill the aging periods of an item with purchases (moving from later to earlier, i.e. youngest first) but not exceeding total quantity on stock. There are several warehouses.  Stocks could be grouped. In that case quantities on stock are summarized and all purchases between stocks are ignored (we will have a clear picture off items, purchased for group). For those purposes I created variable, what concatenates selected or possible warehouses.

      In real situation there are a lot of additional tables. That's why relations between tables are as they are.

       

      An issue: If we have an item in dimension, than everything calculates correctly. But looking at result without item dimension (summarized data) we can see some differences in Aging period numbers. That happens 'cause expression in summarized table operates with total sums of stock and purchases ignoring item level.

       

      It is absolutely sure, that an aggregation is needed here. I tried to use it in many ways, but no result. Also I haven't found a better way to distract results of previous periods to have the rest of stock than a distracting column values. It is not the correct way, 'cause column data is for item group but not for each item.

       

      The better way would be using such principle as

      sum(rangemin(aggr(sum(Stock),Stock.Item) ,aggr(sum(Purchases),Stock.Item))),

      but it is not working by me.

       

      Definitely I am doing something wrong.

      You are welcome with any suggestion!