topic Average of aggregated count in QlikView

Average of aggregated count

kourosh_r — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am new to Qlikview and have a requirement to calculate the average count of visitors for the last 6 months based on the user selection of the month.

For instance, If I choose Jan 2016 it should calculate the average of Jan2016, Dec2015,Nov2015,Oct2015,Sep2015,Aug2015.

what I have found and written is this

=Avg(Aggr(count( {<MonthYear={">$(=Date(AddMonths(Max(MonthYear),-6),'MMM-YYYY'))<=$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} Visitor_ID), MonthYear))

If I do not choose any month it works and gives me the average but when a month is selected it returns only the value of selected month not the average for the last 6 months. what am I doing wrong?

Thank you in advance

Re: Average of aggregated count

marcus_sommer — Fri, 27 May 2016 17:29:44 GMT

Try it with:

=Avg(Aggr(count( {1<MonthYear={">$(=Date(AddMonths(Max(MonthYear),-6),'MMM-YYYY'))<=$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} Visitor_ID), MonthYear))

- Marcus

Re: Average of aggregated count

kourosh_r — Fri, 27 May 2016 17:33:09 GMT

Thank you Marcus, but it did not work.

Re: Average of aggregated count

sunny_talwar — Fri, 27 May 2016 17:50:36 GMT

How about this:

=Avg({<MonthYear={">$(=Date(AddMonths(Max(MonthYear),-6),'MMM-YYYY'))<=$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} Aggr(count( {<MonthYear={">$(=Date(AddMonths(Max(MonthYear),-6),'MMM-YYYY'))<=$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} Visitor_ID), MonthYear))

Re: Average of aggregated count

kourosh_r — Fri, 27 May 2016 18:01:00 GMT

Thank you Sunny but I don't understand it. could you please explain it? I tried it but the result is only zero.

Re: Average of aggregated count

sunny_talwar — Fri, 27 May 2016 18:14:44 GMT

You are restricting your inner aggregation using set analysis to show 6 months of data, but the outer aggregation is not restricted. As soon as your select a month, the outer one will just show the result for that month. By adding the same set analysis you used for inner aggregation to the outer one, you are allowing the outer one to display the 6 months also.

Do you have other date related fields where you make selections like Date, Year, Quarter, Week?

Re: Average of aggregated count

sunny_talwar — Fri, 27 May 2016 18:17:01 GMT

Another thing to check is the MonthYear format. Is it MMMYYYY or MMM-YYYY? You post shows Jan2016, but your set analysis uses MMM-YYYY. Make sure the two are consistent, because otherwise your set analysis will not work.

Re: Average of aggregated count

marcus_sommer — Sat, 28 May 2016 02:05:49 GMT

Very well spotted and explained Sunny - a bit more detailed explanation is here: Set Analysis in the Aggr function.

- Marcus

Re: Average of aggregated count

sunny_talwar — Sat, 28 May 2016 10:41:19 GMT

Thanks for sharing this link Marcus‌ I did try to lookup for something that did explain this to support what I mentioned. I am glad that you were able to add this here for us.

Re: Average of aggregated count

kourosh_r — Tue, 31 May 2016 11:49:47 GMT

Hi Sunny,

Thank you so much for taking the time and explaining this to me, I understand it now. As you've mentioned I have other date related fields as well. in addition to the MonthYear filed I have Month, Year and Quarter that I make the selection. When I use the MonthYear filed, your suggestion works beautifully but when I use only the Month, I get the value for that very Month. Is there any remedy to this? Thank you in advance.

-Kourosh

Re: Average of aggregated count

kourosh_r — Tue, 31 May 2016 11:51:54 GMT

Hi Marcus,

Thank you for your time and sharing this link. It is well explained by Henric.

Regards,

-Kourosh

Re: Average of aggregated count

kourosh_r — Tue, 31 May 2016 11:56:46 GMT

Hi Sunny,

Sorry about that It was my typos in posting the problem. the format is 'MMM-YYYY' for the MonthYear field.

Best

-Kourosh

Re: Average of aggregated count

oknotsen — Tue, 31 May 2016 12:00:26 GMT

If your question is now answered, please flag the Correct Answer and optionally Helpful Answers.

If not, please let us know with which part of this topic you still need help with .

Re: Average of aggregated count

kourosh_r — Tue, 31 May 2016 12:10:01 GMT

It is partially answered by Sunny but it is not the completely resolved. I am marking his answer as correct but still I need to know how could I get the same result when I select the Month filed instead of MonthYear. That was my hesitation for not flagging the Correct Answer.

Thanks,

-Kourosh

Re: Average of aggregated count

sunny_talwar — Tue, 31 May 2016 12:27:34 GMT

You can try this:

=Avg({<MonthYear={">$(=Date(AddMonths(Max(MonthYear),-6),'MMM-YYYY'))<=$(=Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Quarter, Year>} Aggr(count( {<MonthYear={">$(=Date(AddMonths(Max(MonthYear),-6),'MMM-YYYY'))<=$(=Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Quarter, Year>} Visitor_ID), MonthYear))

Add all other date related fields where you might make a selection.

Re: Average of aggregated count

sunny_talwar — Tue, 31 May 2016 12:28:12 GMT

Look at my response above

Re: Average of aggregated count

kourosh_r — Tue, 31 May 2016 12:39:29 GMT

Perfect. It was exactly what I was looking for!!!

I would appreciate it if you could tell me why adding these fields changed the behavior of the selection when they are not selecting anything.

Regard,

-Kourosh

Re: Average of aggregated count

kourosh_r — Tue, 31 May 2016 13:39:05 GMT

I understood it , I was not clear about omitting the equal sign in front of a filed was an option. Thanks again!

-Kourosh