Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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