Count Distinct based on another field

gardan — Wed, 18 Aug 2010 20:53:29 GMT

Here is a sample of my data:

DeliveryID,Origin,Destination,ProductID,Quantity,Distance
33613333,US66,6100456,4055,540,40
33613344,US44,6100589,4055,240,84
33613344,US44,6100589,4003,300,84
33613355,USKL,6100666,4055,540,32
33613377,USPP,6100788,4003,250,76
33613377,USPP,6100788,4055,290,76

The first Delivery had 540 of Product 4055 on a truck that went 40 miles.

The second delivery had 240 of product 4055 and 300 of product 4003 on the truck that went 84 miles.

Now to count the number of loads, I can write the simple expression: Count(DISTINCT DeliveryID) and the answer would be 4

My question is how is the best way to calculate the average distance the 4 deliveries ran.

If my expression is Avg(Distance) would it not weight those deliveries that delivered two diffent products twice as much as the ones that delivered one product?

Count Distinct based on another field

Wed, 18 Aug 2010 21:16:11 GMT

you could use the aggr function like so:


aggr(avg(Distance), DeliveryID)

Which would return 58 (the average of the 4 unique trips) but you would also need to change your charts total mode to "average of rows" instead of expression total

Count Distinct based on another field

gardan — Thu, 19 Aug 2010 12:09:31 GMT

I never thought of using the aggr() function. thanks!!

topic Count Distinct based on another field in QlikView

Count Distinct based on another field

Count Distinct based on another field

Count Distinct based on another field