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: 
bharatkishore
Creator III
Creator III

3 Months Rolling in script

Hi All,

I have few few fields and an  yearmonth column and ITM field.

Year Month i have years from 201601 to 201802.

And for ITM field i don't have any data for whole year 2016.

Now i am calculating 3 month rolling for that i have given the below expression:

RangeSum(Test,Peek(Test,-1),peek(Test,-2)) as [3Month]

The output is getting what i am expected but i am getting data for 2016 also.

Can you please let me know what is the issue.

Attached app for reference

Thanks,

Bharat

1 Solution

Accepted Solutions
sunny_talwar

Check with this

RangeSum(

Test,

If(Key = Peek(Key, -1), Peek(Test,-1)),

If(Key = Peek(Key, -2), peek(Test,-2))

) as [3Month],

View solution in original post

15 Replies
sunny_talwar

I think you are not doing a check of If(Key = Previous(Key)...

Capture.PNG

Try reloading the attached file and see if it looks better

bharatkishore
Creator III
Creator III
Author

Sunny Bhai,

Now i am getting for 201602.

PFA app Sunny Bhai

sunny_talwar

Would you be able to share this

Modality_Report_temp14.qvd

bharatkishore
Creator III
Creator III
Author

PFA qvd .

sunny_talwar

Check with this

RangeSum(

Test,

If(Key = Peek(Key, -1), Peek(Test,-1)),

If(Key = Peek(Key, -2), peek(Test,-2))

) as [3Month],

bharatkishore
Creator III
Creator III
Author

Thank you Sunny Bhai. Can you please tell me why we need to like that.

I mean like the below one two times

If(Key = Peek(Key, -1), Peek(Test,-1)),



Do we need to need like this for 3 months or is there any other method?

Please let me know..

sunny_talwar

Every Peek(..., Number)... will need to check Peek(Key, Number)... this is needed to make sure that when we are adding things from previous rows, it only adds when the Key from that row matches with the key from current row.

bharatkishore
Creator III
Creator III
Author

Thank you Sunny Bhai.

Just one thing is there any other method to calculate rolling 3 months. because i need to for almost 10 fields.

sunny_talwar

This is probably the most efficient way to do it... but you can use multiple joins to do this as well.