Average on count of the whole loaded data

[Karen_Gumerov]

Hi. I'm trying to calculate average value on count of a field so that it would take the whole loaded data.

For example, I need to take average on count of sales by date. This works fine unless I apply filters:

Avg(Aggr(Count(sales), date))

If I apply filtering, the average will be recalculated, but I need it to be constant among the whole data. So I tried using

Avg(ALL Aggr(Count(sales), date))

But it didn't work. Is that possible at all in QlikSense without primary grouping on query level?

Any help is highly appreciated.

Thanks in advance!

[sunny_talwar · Accepted solution]

Try this

Avg({1} Aggr(Count({1} sales), date))

[Karen_Gumerov]

Thank you, Sunny! This is what I was looking for.

I can successfully get this average in a KPI chart, but when it comes to bar or combo chart, I see a strange broken line instead of straight line. Would you have any ideas about that?

[sunny_talwar]

It seems you have no data from Sep/Oct 2014 till June/July 2016... may be that is what is causing it?

[Karen_Gumerov]

I don't think that's the case. I mean, it must be a straight line across the whole chart. Anyway I managed to do it as a reference line in line chart. Unfortunately there are no reference lines available for combo charts, and I couldn't add such an average value as a measure.

[sunny_talwar]

May be share you qvf for us to take a look at?

[Karen_Gumerov]

Hi Sunny,
I attached my qvf with some dummy data. Anyway the problem there still exist. Using one and the same formula in different charts I get different results. In KPI chart it shows real average of the whole data, but in combo chart red line, which is supposed to show the same constant value, behaves as if it is composed of average values at each point.

The used formula is Avg({1} Aggr(Count({1} tran), dt))

[sunny_talwar]

Try this

=Avg({1} TOTAL Aggr(Count({1} tran), dt))

or this

=Avg(TOTAL Aggr(Count(tran), dt))

or this

=Avg(TOTAL Aggr(Count({1} tran), dt))

[Karen_Gumerov]

Hello Sunny,

That was really helpfull. Thanks.

But can we go more advanced and apply average line depending on month here?

For example, if I have data for two months and I need a separate part of average line for each. So that when I select a specific month in filter pane, I get straight line for this month.

This is what I expect:

I tried to apply formula =Avg({1} TOTAL Aggr(Count({1} tran), [dt.autoCalendar.Month])), but it didn't work.

I attached my qvf with sample data. Could you please have a look? Thanks a lot.

[sunny_talwar]

Try this

=Aggr(NODISTINCT Avg({1} Aggr(Count({1} tran), [dt.autoCalendar.Month], dt)), [dt.autoCalendar.Month])