2 Replies Latest reply: Dec 11, 2013 11:22 AM by Josh Good RSS

    Aggregation of Inventory Across Time Dimension

      I have a problem where the requirement is to reflect an "ending inventory" for each dimension of a calendar hierarchy. So, in other words, when I view a "Month's" inventory, I am viewing the last week of that Month's inventory (not the sum of ALL of the weeks of inventory for that month). And the same would apply for a Quarter, Season or Year. I DO want to "sum" the inventory across other "hierarchies" like products and locations. An illustration from my app is provided below. The values for the week 3/30/13 should be reflected in the Total March (which is actually reflecting the SUM of each week). I am guessing a variation of "Aggr" would be used but not sure how to approach. Thank you for any help in advance.

       

      Inv Snapshot2.jpg

        • Re: Aggregation of Inventory Across Time Dimension
          Tim Benoit

          You are correct - you cannot usually sum inventory quantities like other things although, depending on the requirements, sometimes you can average the inventory quantities.

          If you have weekly inventory data and you want to sum up quantities but only for the last week week of the month you can do something like this:    sum( if(Month(invdate)<>Month(invdate+7), invquantity)  ) 

          The idea works if you use the Year function instead of Month to get the last week of the year. And you could use something like this    sum( if(QuarterName(invdate)<>QuarterName(invdate+7), invquantity)  )   to sum inventories from the last week of the quarter.

          • Re: Aggregation of Inventory Across Time Dimension
            Josh Good

            You likely could create some sort of complex equation however in my opinion that would be the 'hard way' to achieve this.  Since you have a defined hierarchy with only 3 level, I would recommend going with three charts and using buttons to show/hide the charts.

             

            Chart 1 would be at the Year Level

            Chart 2 would be at the Month Level

            Chart 3 would be at the Week Level

             

            Pushing the "Month Level" button would show Chart 2 and hide Chart 1 & 3.

             

            This will also have the advantage of taking up less header space with the expanding pivot table.  Also it will be easier for users to move between levels because they will have a button to push vs needing to hit the little expand buttons in the pivot table.

             

            A similar technique is used on the Order and Inventory Management Demo.

             

            You may also want to check out this video to see the general show/hide technique:

            Show and Hide Multiple Objects