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

[kourosh_r]

Hi Marcus,

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

Regards,

-Kourosh

[kourosh_r]

Hi Sunny,

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

Best

-Kourosh

[oknotsen]

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 .

[kourosh_r]

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

[sunny_talwar · Accepted solution]

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.

[sunny_talwar]

Look at my response above

[kourosh_r]

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

[kourosh_r]

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

-Kourosh