Average of aggregated count

[kourosh_r]

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

[marcus_sommer]

Try it with:

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

- Marcus

[kourosh_r]

Thank you Marcus, but it did not work.

[sunny_talwar]

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

[kourosh_r]

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

[sunny_talwar]

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?

[sunny_talwar]

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.

[marcus_sommer]

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

- Marcus

[sunny_talwar]

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.

[kourosh_r]

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