topic Re: Total Dead Inventory On Hand using Sum If with AGGR in QlikView

Total Dead Inventory On Hand using Sum If with AGGR

Tue, 04 Aug 2015 19:57:25 GMT

Hi everyone,

I'm trying to add up inventory values for set of materials that are classified as dead inventory on hand.

First, I created an AGGR function to determine what is classified as dead inventory.(Months of Inventory On Hand > 36 months)

AGGR(OnHandValue/CostofGoodsSold,MaterialID)

So I want to capture the On Hand Value for all materials that has > 36 months

I was hoping that following would do the trick but I can't seem to make it work:

=sum(if(AGGR(OnHandValue/CostofGoodsSold,MaterialID)>36,OnHandValue),0)

Any ideas please? I tried a second AGGR for OnHandValue to be summed but that didn't do anything either.

Thanks in advance for taking a look.

Re: Total Dead Inventory On Hand using Sum If with AGGR

Tue, 04 Aug 2015 19:59:17 GMT

I forgot to mention that OnHandValue is also a function like

sum(QuantityOnHand*PurchasePrice)

So maybe that's what's causing the problem.

Re: Total Dead Inventory On Hand using Sum If with AGGR

Anonymous — Tue, 04 Aug 2015 20:21:38 GMT

Hi,

Please try below

aggr(sum({<Date = {">=$(=addmonths(Today(),-36))"}>},OnHandValue)/sum({<Date = {">=$(=addmonths(Today(),-36))"}>}CostofGoodsSold),MaterialID)

aggr(sum({<Date = {">=$(=addmonths(Today(),-36))"}>},QuantityOnHand*PurchasePrice)/sum({<Date = {">=$(=addmonths(Today(),-36))"}>}CostofGoodsSold),MaterialID)