Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Avg function not producing the correct value

Qlik Sense Question:

The avg function is not producing the correct value.

I have an app that contains two sheets. The first sheet is made up of visualizations and filters, and the second page is a table which contains every dimension (no measures or calculations).

I select my desired filters, and then confirm the total amount of rows on the second sheet's table (it correctly matches the total counts from the first page). One column in this table is annual_salary. If I use the average function (annual_salary), I receive a number that does not match excel's average function on the same column.

Note:

1. The table is set to display all null values.

2. The column does not contain any 0's, or nulls.

Has anyone encountered similar issues with the avg function?

1 Solution

Accepted Solutions
sunny_talwar

How about this:

Avg(Aggr(Only({<COMPENSATION_TYPE = {'*Salaried*'}>} ANNUAL_SAL), Employee_ID))

View solution in original post

8 Replies
sunny_talwar

Looking here might help:

Average – Which average?

mgranillo
Specialist
Specialist

Chris,

I would double check the raw data.  The average function will calculate over the number of table rows.

Nulls are also discarded from the calculation.  for example:

Column:

[2

3

4

null]

Result from the average function would be 3. 

zero values should be included.  You check method looks valid and I would expect a match.  Just double check your work.

Mike

swuehl
MVP
MVP

What do you mean with

'(it correctly matches the total counts from the first page)'?

What is exactely shown at both places?

Note that e.g. a table box on second sheet won't show every record, only distinct combinations of dimensional values.

Not applicable
Author

Please screen shots below. In the second screen shot, I see an average salary of 72,457. If you average the salaries listed in the 4th screen shot, that is not the true average.

Dashboard Screen Shot.jpg

Pivot Table Screen Shot.jpg

Expression Screen Shot.jpg

Table Screen Shot.jpg

sunny_talwar

How about this:

Avg(Aggr(Only({<COMPENSATION_TYPE = {'*Salaried*'}>} ANNUAL_SAL), Employee_ID))

Not applicable
Author

Yessssss, thank you, that did it.

You are my hero. You all need an online tip jar, you just saved me a ton of headache.

Thanks again,

Chris

sunny_talwar

Great

Please close this thread by marking correct and helpful responses, if you got what you were looking for.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

vijayUV
Contributor II
Contributor II

The data set hasDeptName, EmpName column and the TAT(Turn around Time in Hrs) columns, we are calculating the Min.TAT, Max.TAT and the Avg.TAT

Avg(TAT) function or Sum(TAT)/Count(TAT)

Both are giving results that are incorrect when I calculate manually. 

Can you please throw some light on this.

Thanks,

Vijay