Solved: Re: Help needed on Rolling 13 months with Aggr() - Qlik Community

Report Inappropriate Content · ‎2017-06-20

Hi All,

Hope everyone is good.

I need your help with the rolling 13 months data with aggr ().

I have a requirement like i need to display avg customer score for rolling 13 months. For that i have used Avg({<Year=,Month=,

Date={">=$(=AddMonths(Max(Date),-13))"}<=$(=Max(Date))>}Score). with the expression values are not coming correctly as the customer code is repeated for multiple rows due to which the avg value is wrong.

Then i have changed the expression to include aggr () as below.

Avg(Aggr(Avg({<Year=,Month=,Date={">=$(=AddMonths(Max(Date),-13))"}<=$(=Max(Date))>}Score),CustomerID).

With the above expression i am able to achieve the correct value but i am not able to display rolling 13 months. The chart i am using is line chart.

Thanks in advance.

Regards,

Nani

sunny_talwar · ‎2017-06-20

This works for the sample

Avg({<Period = {"$(='<=' & Max(Period))"}>} Aggr(Avg({<Period = {"$(='<=' & Max(Period))"}>}Score), ID, Period))

View solution in original post

sunny_talwar · ‎2017-06-20

Not sure, but may be this

Avg({<Year=,Month=,Date={">=$(=AddMonths(Max(Date),-13))"}<=$(=Max(Date))>} Aggr(Avg({<Year=,Month=,Date={">=$(=AddMonths(Max(Date),-13))"}<=$(=Max(Date))>}Score),CustomerID)

Report Inappropriate Content · ‎2017-06-20

Thanks for the reply Sunny. Let me check and confirm.

Regards,

Nani

Report Inappropriate Content · ‎2017-06-20

Hi Sunny,

This is showing the rolling 13 months in the dimension but the values are not correct.

I need to display average score by month. but when i am using the expression the values are not matching.

Thanks,

Nani

sunny_talwar · ‎2017-06-20

Would you be able to share a sample to check out?

Report Inappropriate Content · ‎2017-06-20

Sure i will share sample app in 10 mins.

Thanks,

Nani

Report Inappropriate Content · ‎2017-06-20

Hi Sunny,

Please find the attached app with some sample data.

As per my need when i select 1702 period i should show both 1701 and 1702 with avg values for 1701: 20 and 1702:25

in the app ID is Customer ID which have the score repeated. due to other columns i can't aggregate in data.

Thanks,

Nani

sunny_talwar · ‎2017-06-20

This works for the sample

Avg({<Period = {"$(='<=' & Max(Period))"}>} Aggr(Avg({<Period = {"$(='<=' & Max(Period))"}>}Score), ID, Period))

Report Inappropriate Content · ‎2017-06-20

Perfect.Thanks for the help Sunny. Much Appreciated.

Regards,

Nani

Report Inappropriate Content · ‎2017-06-20

Can i use rolling 13 months logic for rolling 13 months in place of "<=Max(Period)" ??