11 Replies Latest reply: Aug 20, 2018 10:04 AM by Sunny Talwar

# 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.

• ###### Re: Average on count of the whole loaded data

Try this

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

• ###### 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?

• ###### 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?

• ###### 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.

• ###### Re: Average on count of the whole loaded data

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

• ###### 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.

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

• ###### Re: Average on count of the whole loaded data

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.

• ###### 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])

• ###### Re: Average on count of the whole loaded data

Hello Sunny,

Could you please explain the syntax of this formula in more detail. It became quite complicated now. For example, if I need to add more fields to aggregation, I have to understand each single part very well.

I tried to decompose it to several levels as follows:

Aggr

(

NODISTINCT

Avg

(

{1}

Aggr

(

Count

(

{1}

tran

),

[dt.autoCalendar.Month],

dt

)

),

[dt.autoCalendar.Month]

)

My questions are:

1. The outer Aggr function has two parameters as Avg and [dt.autoCalendar.Month]. Why not three parameters as inner Aggr? What is attribute NODISTINCT for in front of Avg function?
2. What does attribute {1} mean near the inner Aggr function?
3. Inner Aggr function has three parameters. Why are there [dt.autoCalendar.Month] and dt at the same time? Does it mean that we aggregate by month and date field?
4. Similar question to 2 about attribute {1} inside Count function.

• ###### Re: Average on count of the whole loaded data

My questions are:

1. The outer Aggr function has two parameters as Avg and [dt.autoCalendar.Month]. Why not three parameters as inner Aggr? What is attribute NODISTINCT for in front of Avg function?

Not sure I follow the first part of the question, but you can read about NODISTINCT here What NODISTINCT parameter does in AGGR function?

2. What does attribute {1} mean near the inner Aggr function?

It is just set analysis used to ignore selections. Read about set analysis here Set Analysis: syntaxes, examples

3. Inner Aggr function has three parameters. Why are there [dt.autoCalendar.Month] and dt at the same time? Does it mean that we aggregate by month and date field?

The best way to understand Aggr() function is to create a straight table with Aggr() dimensions as your dimension and inner expression within Aggr() as your expression. The challenges of getting the right average is what made me choose the expression...

4. Similar question to 2 about attribute {1} inside Count function.

Same reason as 2. above