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: 
debmsarkar123
Contributor III
Contributor III

Null values are being used when doing average

I have a chart where we are calculating Avg Time metric. Ideally we want it to be (14.9 +30.2)/2= 22.6 but its showing 15

 

debmsarkar123_0-1645543546004.png

In chart properties we have kept total mode as Average of rows but still its taking the rows which have zero or - values into consideration when doing average.

The expression Im using currently- 

sum({<Year= {'$(vMaxYear)'}>}[X)])
/
sum({<Year= {'$(vMaxYear)'}>}[Y])

and the chart properties is-

 

debmsarkar123_0-1645558251652.png

 

 

Labels (3)
3 Replies
hic
Former Employee
Former Employee

NULLs are never included in the calculation, but zeros are. 

In your data, the first row has an avg time of 0.0, and this of course affects the total average. If you want to exclude this from the calculation you could try to change your measure to 

Avg(If(Time>0,Time))

instead of 

Avg(Time)

debmsarkar123
Contributor III
Contributor III
Author

Hi,

Thanks for reply. I have updated the question with the expression and chart properties Im using currently. Could you please suggest a modification of that?

hic
Former Employee
Former Employee

So you use a ratio between two sums as measure, and then choose "Average of rows" as total mode. That is perfectly OK.

However it doesn't change the main problem: You still have "Avg Time (mins)" = 0.0 on the first row of your straight table, and this number is correctly included in the average. The average IS 15.0.

If you don't want to include this row, you must exclude data where [X] is zero, for example through

sum({<Year={'$(vMaxYear)'},X={">0"}>} X )
/
sum({<Year={'$(vMaxYear)'}>} Y )