Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I have a straight table, with 8 columns. I'd like to work out the average of these 8 columns but excluding zeros as this will skew the count.
This works well, but gives me skewed average due to the zeros within columns:
(Column(1)+Column(2)+Column(3)+Column(4)+Column(5)+Column(6)+Column(7)+Column(8))/8
Thanks guys.
Obviously you can use if conditions. But if you need a solution without if condition then use the below expression. This might solve your problem.
(Column(1)+Column(2)+Column(3)+Column(4)+Column(5)+Column(6)+Column(7)+Column(8))
/
(-1*( (column(1)>0) + (column(2)>0) + (column(3)>0) + (column(4)>0) + (column(5)>0) + (column(6)>0) + (column(7)>0) + (column(8)>0) ))
I would believe you are likely going to have to use Set Analysis in that case to accomplish things:
https://community.qlik.com/t5/Qlik-Design-Blog/Excluding-values-in-Set-Analysis/ba-p/1471704
That post may get you on the right track, here is the base Design Blog URL in case you want to search for other related posts there:
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
Regards,
Brett