Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Strange behavior calculating Average

What about

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

4 Replies
MVP
MVP

Re: Strange behavior calculating Average

What about

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

Not applicable

Re: Strange behavior calculating Average

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
Valued Contributor III

Re: Strange behavior calculating Average

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

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

MVP
MVP

Re: Strange behavior calculating Average

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.