topic Re: Excluding rows in expression in QlikView

Excluding rows in expression

Tue, 15 May 2012 18:57:26 GMT

I calculate an average on a chart on our clients in a multi chart that cycles thru different expressions.

One of the expressions is the average miles driven by Carrier for each client.

Sum(Miles)/count(ShipmentID)

This issue I have is that some shipments have zero miles and i don't want to skew the average and want to reduce my count by those shipment counts.

Any ideas on how to reduce the count to reflect the true shipments with miles > zero. thanks, Bill

Re: Excluding rows in expression

Nicole-Smith — Tue, 15 May 2012 19:34:24 GMT

Using ({<Miles={">0"}>} as the set analysis in your expression, it will only take the amounts where Miles > 0 (if you don't want it on both the sum and count like I have below, you can have it on just one and not the other).

=sum({<Miles={">0"}>}Miles)/count(({<Miles={">0"}>}ShipmentID)

Re: Excluding rows in expression

Tue, 15 May 2012 20:05:54 GMT

Thanks Nicole, I only need to modify the count so this is the syntax that I used.

sum(Miles)/count({<Miles={">0"}>}ShipmentID)

Worked great.