
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count distinct vs Count total distinct
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
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
