Count distinct vs Count total distinct

[ahmedwank]

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

[stevedark · Accepted solution]

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