topic Re: 3 Months Rolling in script in QlikView

3 Months Rolling in script

bharatkishore — Fri, 16 Mar 2018 13:17:21 GMT

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

Re: 3 Months Rolling in script

sunny_talwar — Fri, 16 Mar 2018 13:23:27 GMT

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

Try reloading the attached file and see if it looks better

Re: 3 Months Rolling in script

bharatkishore — Fri, 16 Mar 2018 13:34:00 GMT

Sunny Bhai,

Now i am getting for 201602.

PFA app Sunny Bhai

Re: 3 Months Rolling in script

sunny_talwar — Fri, 16 Mar 2018 13:35:49 GMT

Would you be able to share this

Modality_Report_temp14.qvd

Re: 3 Months Rolling in script

bharatkishore — Fri, 16 Mar 2018 13:49:40 GMT

PFA qvd .

Re: 3 Months Rolling in script

sunny_talwar — Fri, 16 Mar 2018 13:57:19 GMT

Check with this

RangeSum(

Test,

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

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

) as [3Month],

Re: 3 Months Rolling in script

bharatkishore — Fri, 16 Mar 2018 14:02:59 GMT

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..

Re: 3 Months Rolling in script

sunny_talwar — Fri, 16 Mar 2018 14:06:18 GMT

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.

Re: 3 Months Rolling in script

bharatkishore — Fri, 16 Mar 2018 14:09:50 GMT

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.

Re: 3 Months Rolling in script

sunny_talwar — Fri, 16 Mar 2018 14:11:17 GMT

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

Re: 3 Months Rolling in script

bharatkishore — Fri, 16 Mar 2018 14:16:29 GMT

Thank you Sunny Bhai.

Sorry to ask again one question. Now i am calculating rolling 3 months if i want to calculate last year rolling 3 months..Can you please suggest me how to do.

Sorry to ask again.

Re: 3 Months Rolling in script

sunny_talwar — Fri, 16 Mar 2018 14:17:35 GMT

Instead of going back 0, -1, -2.... go back -12, -13, -14

Re: 3 Months Rolling in script

bharatkishore — Fri, 16 Mar 2018 14:21:33 GMT

Thank you Sunny Bhai.. Thank you so much for your help and time..

Re: 3 Months Rolling in script

sunny_talwar — Fri, 16 Mar 2018 14:22:20 GMT

No problem

Re: 3 Months Rolling in script

sunny_talwar — Fri, 16 Mar 2018 14:23:02 GMT

Also, look into The As-Of Table ... as this may be a good alternative...

Re: 3 Months Rolling in script

bharatkishore — Fri, 16 Mar 2018 14:26:55 GMT

Sure Sunny Bhai..