topic Re: Aggregation of Inventory Across Time Dimension in QlikView

Aggregation of Inventory Across Time Dimension

Thu, 11 Apr 2013 15:42:16 GMT

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.

Re: Aggregation of Inventory Across Time Dimension

Fri, 12 Apr 2013 18:24:50 GMT

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 — Wed, 11 Dec 2013 16:22:50 GMT

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