Total Dead Inventory On Hand using Sum If with AGGR

[#1]

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.

[#2]

I forgot to mention that OnHandValue is also a function like

sum(QuantityOnHand*PurchasePrice)

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

[Anonymous]

Hi,

Please try below

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

or

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