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

Strange behavior calculating Average

Hello, im relatively new to qlik and im having trouble to find the correct expression to calculate an average.

I'm trying to draw an avg line, sum of sales per count of years, in a combinated chart, but the result im getting is wrong.This is the expression im using : Sum(Total(LINEPRICE))/count(Distinct(ANO)).

avgfail.png

As you can see, in a KPI object, count(distinct(ANO)) is working correctly, but not in the chart. Now if i change the expression in chart to Sum(Total(LINEPRICE)) / 11 i get the result as i expected:

avgsuccess.png

Can someone, please, tell me what im doing wrong?


Thanks in advance and sorry for my english.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

What about

Sum(Total LINEPRICE)/count(TOTAL Distinct ANO)

View solution in original post

4 Replies
swuehl
MVP
MVP

What about

Sum(Total LINEPRICE)/count(TOTAL Distinct ANO)

Not applicable
Author

Thanks a lot, that made it.

Can you, please, tell me why in KPI objec it worked as i intended but in chart i had to put the Total together with the distinct?

simondachstr
Luminary Alumni
Luminary Alumni

Because in the line chart you are splitting your calculation by each year(ano).

count(Distinct ANO) will return one value for each year.

swuehl
MVP
MVP

The aggregation scope is different in both cases.

The Aggregation Scope

If you use Count(DISTINCT ANO) in a text box, it will do a count distinct using all possible ANO values.

If you use it in a chart with a dimension, it will only do the distinct count for the possible values with a relation to the dimensional value. If you are using ANO as dimension, this will return 1 for all dimensional values.