Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Rangesum - input/output calculation

Hi Qlik Experts.

I'm having an issue with one of my expressions regarding the rangesum expression.

What I would like to do is to make an expression with an accumulative difference between input and output which should be my stock. Let's say I have the following input and output. In February my stock is 8 based on my activities in January and February. If I want to see the stock for only the last three month then the result is 6, 6 and 14 and not -2, -2, 6 because the stock for the last three month must include what was in stock before the period shown.

 

 

MonthInputOutputDifferenceIn stock
Jan10555
Feb10738
Mar810-26
Apr151506
May2012814

In the attached qvw-file I have four charts. In the last chart named "Stock / weeks" I want to show the stock in the last 12 weeks based on all input and output but my chart unfortunately only shows the accumulated difference in input and output for the last 12 weeks. My stock in YYYYMM = 201814 is 55.925,1 so the result I get in my chart should be added with 55.925,1 in all 12 months to be correct.

I have tried to add the sum from YYYYMM = 201814 to my expression but then the x-axis shows all months and not only the last 12.

The result in the ultimo period in chart "Stock / months" and chart "Stock / weeks" must the same because it's the last known stock. In chart "Stock / months" the result for YYYYMM 201806 is 97.420,3 which is correct. The result in chart "Stock / weeks" for YYYYWW 201826 should be exactly the same.

Therefore I hope one of you experts out there know how to fix it so only the last 12 months are shown in the chart unless less is selected, and also that the "real stock" is calculated and not just the accumulated difference for the selected period.

Best Regards,

Johnni Kirstein

3 Replies
ogautier62
Specialist II
Specialist II

Hi,

you can use just as expression for In Stock :

= difference + above(In Stock)

to add initial stock before replace your dimension YearMonth with :

if(yearMonth < 'your period not in graph', 'Stock Initial', YearMonth)

regards

Anonymous
Not applicable
Author

Hi Olivier.

Thank you for your response.

I'm not sure that I understand your solution. Could you please attach it to the qvw-file I made for this post?

I have tried the expression and calculated dimension. The expression does not work, probably because I do something wrong. When I do the calculated dimension, I get a value in the beginning of the dimension.

Best Regards,

Johnni

ogautier62
Specialist II
Specialist II

just now I havn't Qlik on my PC

modify expression :

difference + if(rowno() = 1,0,above([In Stock]))

I thought it would be 0, but for first row, above not exists