Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
vamshi_1241
Partner - Creator
Partner - Creator

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

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

 

2 Solutions

Accepted Solutions
vamshi_1241
Partner - Creator
Partner - Creator
Author

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)

View solution in original post

vamshi_1241
Partner - Creator
Partner - Creator
Author

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

View solution in original post

6 Replies
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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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

vamshi_1241
Partner - Creator
Partner - Creator
Author

This is just a formula.

 

sunny_talwar

I am not sure I follow

vamshi_1241
Partner - Creator
Partner - Creator
Author

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