Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I forgot to mention that OnHandValue is also a function like
sum(QuantityOnHand*PurchasePrice)
So maybe that's what's causing the problem.
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)