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

Creating average of row in column

Iterm codeVendor1Vendor2Average
A10.5010.20
B9.339.00
C8.217.35
D5.365.05

In straight table , how am i calculating the value for the column "Average" ? - That is average price offered by both of the vendor for that particular item code

1 Solution

Accepted Solutions
Gysbert_Wassenaar

0 is a value, null is not. RangeAvg does not consider null because it's not a value. If you want to disregard 0 then use RangeAvg(If(Vendor1<>0,Vendor1),If(Vender2<>0,Vendor2). And <> 0 means <> exactly 0, not <> 0.00000000000023.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

RangeAvg(Vendor1, Vendor2)

Or (Vendor1+Vendor2)/2


talk is cheap, supply exceeds demand
limfungkeat
Contributor III
Contributor III
Author

thanks Gysbert,

The reason why i am not using the scond one (i rather straight forward thought..), is that , what is there is null value in the vendor1/ vendor 2 ?

limfungkeat
Contributor III
Contributor III
Author

the RangeAvg() is taking 0 / null value into the calculation, is there any way to exclude null/0 value ? Ie if vendor1 - 10.50 ; vendor2- 0 , then the calculation should divide by 1 not 2 .

Gysbert_Wassenaar

0 is a value, null is not. RangeAvg does not consider null because it's not a value. If you want to disregard 0 then use RangeAvg(If(Vendor1<>0,Vendor1),If(Vender2<>0,Vendor2). And <> 0 means <> exactly 0, not <> 0.00000000000023.


talk is cheap, supply exceeds demand
limfungkeat
Contributor III
Contributor III
Author

Thanks a lot Gysbert ! Well, i actually convert my source with null value to 0 and it works fine