Inventory count

Tue, 09 Jun 2015 14:24:20 GMT

Hi! I'm running into something I just can't wrap my head around how to get it to work. What I am building (or at least trying to) is a chart with shops who have done their inventory counts. Unfortunately each shop has a different number of past inventories. What I want to create is a chart with the most recent count for each shop per item counted. so something like this:

[Item 1] [Item 2] [Item 3] [etc]

Shop 1 X X X X

Shop 2 X X X X

Shop 1 might have max(inventory_number)=24

Shop 2 might have max(inventory_number)=46

So for each item I want to show the result of the lastest count. I was thinking of adding an expression (max(inventory_number)) and then using that expression in a set analysis, but that returned only 1 shop to me, the one with the highest inventory_number, not a result per shop.

Could anyone get me on the way to solving this?

Thanks!

Re: Inventory count

rubenmarin — Tue, 09 Jun 2015 14:30:02 GMT

Hi Gregoor, set analisys is calculated before the table, so it won't work row by row. Maybe using Aggr() does the trick:

Aggr(If(inventory_number=max(inventory_number), ItemCountField), ShopField)

or:

Aggr(If(inventory_number=max(inventory_number), ItemCountField), ShopField, ItemField)

If ItemCountField has more than one value for a shop-item-inventory_number, you'll need to encolse Aggr() and/or ItemCountField in an aggretion function like Sum(), Avg()...

topic Inventory count in QlikView

Inventory count

Re: Inventory count