3 Months Rolling in script

[bharatkishore]

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

[sunny_talwar]

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

Try reloading the attached file and see if it looks better

[bharatkishore]

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]

PFA qvd .

[sunny_talwar · Accepted solution]

Check with this

RangeSum(

Test,

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

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

) as [3Month],

[bharatkishore]

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]

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.