Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
I need help to calculate the current month +previous quarter last month inv value (dynamic). I need this in script level because I got it to form front end. I need to do more calculation once I get this value from the backend
I tried with rangesum(Value, peek('Value', -3) but it is showing the exact three months from current month. But I need calculation like below,
May Net Inv should be: May Net Inv + March Net Inv.
and April should be: Apr Net Inv + March Net Inv
and March should be: March Net Inv+Dec Net Inv
Thanks,
vamshi
Business wants to calculate DIOH – Days Inventory On Hand, they suggested a calculation that is :
Annual COGS/((Current Month + Prior Quarter Last Period)/2)
Here, Annual COGS is the sum of Current Month+ Previous 2 months (Ex: May+April+March)
I tried with below expression and it is working fine,
rangesum(Net Inv, peek('Net Inv', if(match(left(FiscalPeriod, 3), 'Dec', 'Mar', 'Jun', 'Sep'), -3,
if(match(left(FiscalPeriod, 3), 'Aug', 'May', 'Feb', 'Nov'), -2, if(match(left(FiscalPeriod, 3), 'Jul', 'Apr', 'Jan', 'Oct'), -1)))))/2 as Inv_Value
Thanks,
Vamshi
This doesn't seem right...
May Net Inv should be: May Net Inv + March Net Inv <- May + Mar? Why not May +Apr?
and April should be: Apr Net Inv + March Net Inv <- This looks okay
and March should be: March Net Inv+Dec Net Inv <- Why Mar + Dec? Why not Mar + Feb?
Business wants to calculate DIOH – Days Inventory On Hand, they suggested a calculation that is :
Annual COGS/((Current Month + Prior Quarter Last Period)/2)
Here, Annual COGS is the sum of Current Month+ Previous 2 months (Ex: May+April+March)
this is just a formula not answer. Accidently selected as answer
This is just a formula.
I am not sure I follow
I tried with below expression and it is working fine,
rangesum(Net Inv, peek('Net Inv', if(match(left(FiscalPeriod, 3), 'Dec', 'Mar', 'Jun', 'Sep'), -3,
if(match(left(FiscalPeriod, 3), 'Aug', 'May', 'Feb', 'Nov'), -2, if(match(left(FiscalPeriod, 3), 'Jul', 'Apr', 'Jan', 'Oct'), -1)))))/2 as Inv_Value
Thanks,
Vamshi