Sum of Current Month + Previous Quarter Last Month in script level

[vamshi_1241]

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

[sunny_talwar]

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?

[vamshi_1241 · Accepted solution]

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)

[vamshi_1241]

this is just a formula not answer. Accidently selected as answer

[vamshi_1241]

This is just a formula.

[sunny_talwar]

I am not sure I follow

[vamshi_1241 · Accepted solution]

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