Inventory count

[#1]

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!

[rubenmarin1]

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()...