topic Re: Help needed on Rolling 13 months with Aggr() in QlikView

Help needed on Rolling 13 months with Aggr()

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help needed on Rolling 13 months with Aggr()

sunny_talwar — Tue, 20 Jun 2017 13:31:13 GMT

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)

Re: Help needed on Rolling 13 months with Aggr()

Tue, 20 Jun 2017 13:39:30 GMT

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

Regards,

Nani

Re: Help needed on Rolling 13 months with Aggr()

Tue, 20 Jun 2017 13:44:32 GMT

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

Re: Help needed on Rolling 13 months with Aggr()

sunny_talwar — Tue, 20 Jun 2017 13:45:51 GMT

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

Re: Help needed on Rolling 13 months with Aggr()

Tue, 20 Jun 2017 13:50:10 GMT

Sure i will share sample app in 10 mins.

Thanks,

Nani

Re: Help needed on Rolling 13 months with Aggr()

Tue, 20 Jun 2017 14:17:51 GMT

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

Re: Help needed on Rolling 13 months with Aggr()

sunny_talwar — Tue, 20 Jun 2017 14:26:30 GMT

This works for the sample

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

Re: Help needed on Rolling 13 months with Aggr()

Tue, 20 Jun 2017 14:37:12 GMT

Perfect.Thanks for the help Sunny. Much Appreciated.

Regards,

Nani

Re: Help needed on Rolling 13 months with Aggr()

Tue, 20 Jun 2017 14:39:25 GMT

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

Re: Help needed on Rolling 13 months with Aggr()

sunny_talwar — Tue, 20 Jun 2017 14:41:01 GMT

I think it should work....

Re: Help needed on Rolling 13 months with Aggr()

Tue, 20 Jun 2017 14:47:12 GMT

Ok. I will modify the logic and test.