Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
kgkgkgkg
New 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.

Thanks in advance!

1 Solution

Accepted Solutions

Re: Average on count of the whole loaded data

Try this

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

12 Replies

Re: Average on count of the whole loaded data

Try this

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

kgkgkgkg
New Contributor II

Re: Average on count of the whole loaded data

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?
2018-08-07_110219.jpg

Re: Average on count of the whole loaded data

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

kgkgkgkg
New Contributor II

Re: Average on count of the whole loaded data

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.

2018-08-08_102650.jpg

Re: Average on count of the whole loaded data

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

kgkgkgkg
New Contributor II

Re: Average on count of the whole loaded data

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. 2018-08-14_131447.jpg

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

Re: Average on count of the whole loaded data

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

Capture.PNG

kgkgkgkg
New Contributor II

Re: Average on count of the whole loaded data

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:

1323.jpg

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.

Re: Average on count of the whole loaded data

Try this

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


Capture.PNG