Count distinct vs Count total distinct

ahmedwank — Wed, 25 Nov 2020 16:16:04 GMT

Hello all

quick question please : when exactly should i use count(distinct categories) and count (total distinct categories)

when i use the second one, does it consider the filtered parameter i will make on Qlik (for exemple if i select certains years...) ?

thanks

Re: Count distinct vs Count total distinct

stevedark — Sun, 17 Mar 2019 00:44:45 GMT

The TOTAL specifies that the dimension of the table or chart is ignored when doing the calculation. It is useful for getting a percentage contribution in a field. For instance would give the percentage of IDs which fell on that row.

count(DISTINCT ID) / count(DISTINCT TOTAL ID)

To get the total regardless of selections you need to use simple Set Analysis, and insert a {1} in the statement. The following would give you the selected count as a percentage of the whole data set:

count(DISTINCT ID) / count({1} DISTINCT ID)

I would recommend creating a simple table with expressions written all three ways (i.e. plain, with TOTAL and with {1}).

Also, getting counts of rows is more efficient if you create a counter on each row. In your load script have this;

LOAD
1 as RowCount,
...

You can then have sum(RowCount), sum(TOTAL RowCount) and sum({1}RowCount). This is simpler syntax, more efficient and can avoid some oddities around using DISTINCT.

Hope that helps.

Steve

topic Re: Count distinct vs Count total distinct in QlikView

Count distinct vs Count total distinct

Re: Count distinct vs Count total distinct