topic Re: Straight table average using columns excluding zeros in QlikView

Straight table average using columns excluding zeros

jlampard40 — Fri, 06 Dec 2019 12:13:05 GMT

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.

prabhuappu — Fri, 06 Dec 2019 13:15:35 GMT

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

Brett_Bleess — Mon, 16 Dec 2019 22:14:52 GMT

I would believe you are likely going to have to use Set Analysis in that case to accomplish things:

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:

Regards,
Brett