Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excluding rows in expression

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

1 Solution

Accepted Solutions
Nicole-Smith

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)

View solution in original post

2 Replies
Nicole-Smith

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)

Not applicable
Author

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.