topic Re: Count unique values in a table in QlikView

Count unique values in a table

Wed, 15 Jan 2014 15:19:50 GMT

Hi all,

I have a problem:

I have a column with vehicle no's and a column with time not available.

Vehicle 1 is 2 times in the table. Now I want to count this vehicle 1 time instead of 2 times, so it's not

a simple count I guess...? I want to know how many unique vehicles there are in the table.

IN this example there 7 rows but 4 unique vehicles.

I want to calculate the average time not available, for vehicle 1 this is 2+4 hours = 6 = 3 hours average.

What kind of formula can I use for this?

please assist!

Vehicle	Hours not available
1	2
2	4
3	4
1	4
2	4
3	3
4	3

Re: Count unique values in a table

Anonymous — Wed, 15 Jan 2014 15:21:03 GMT

Avg([Hours not available])

Re: Count unique values in a table

Wed, 15 Jan 2014 15:25:08 GMT

ANd how can I count the vehicles then? I want to know that there are 4 unique vehicles in this table.

in this table are 4 unique vehicles, vehicle 1 avg time not available is 3 hours, etc.

Re: Count unique values in a table

Wed, 15 Jan 2014 15:31:25 GMT

=avg(DISTINCT Hoursnotavailable) as the expression and vehicle as the dimension in a bar chart will give you what you are looking for. See attached.

Re: Count unique values in a table

giakoum — Wed, 15 Jan 2014 15:35:44 GMT

create a straight table chart with vehicle as dimension and expressions :

- Avg([Hours not available]) as jeffrey suggested

- count(vehicle) to count how many times it was not available

As it is a chart, every vehicle will show up once

Re: Count unique values in a table

Anonymous — Wed, 15 Jan 2014 15:39:53 GMT

Count(DISTINCT Vehicle)

Although, I'm not entirely clear on what you're trying to accomplish. If you want the average time for each vehicle, then you would create a "Straight Table" chart with Vehicle as the dimension and Avg([Hours not available]) as the expression. If you want to know how many unique vehicles you have then either a Text Box or a "Straight Table" chart with no dimension and the expression Count(DISTINCT Vehicle). I guess that you could put them both in the same chart and keep expression totals on. That way the totals at the top would show the total average and the total count of unique vehicles. Each row of the chart would show the average time and 1 for unique vehicles.

Re: Count unique values in a table

Clever_Anjos — Wed, 15 Jan 2014 15:40:42 GMT

Count(distinct Vehicle) should return what you need