# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a \$200 Amazon Gift Card! Watch Video
cancel
Showing results for
Did you mean:
Contributor II

## Average on count of the whole loaded data

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.

1 Solution

Accepted Solutions
MVP

Try this

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

12 Replies
MVP

Try this

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

Contributor II

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?

MVP

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

Contributor II

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.

MVP

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

Contributor II

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

MVP

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

Contributor II

Hello Sunny,

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.

MVP

Try this

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

Tags