topic Count Distinct Values from Multiple Columns in One KPI in App Development

Count Distinct Values from Multiple Columns in One KPI

HelmickC — Mon, 19 Apr 2021 13:39:00 GMT

Hello,

I am fairly new to developing in Qlik Sense. I am working on creating a KPI to display one value from multiple columns loaded from my script(s). The three columns I have are ADM_CSN, OBV_ADM_CSN and IP_ADM_CSN. Thus far, this is what I have:

=Count(Distinct [ADM_CSN]) + Count({<[IP_ADM_CSN] -= p([ADM_CSN])>}Distinct [IP_ADM_CSN])

I also tried this:

=Count(Distinct([ADM_CSN])) + Count(Distinct([OBV_ADM_CSN])) + Count(Distinct([IP_ADM_CSN]))

These are almost getting me what I need, but not exactly. The first expression does not include my third column and I am not sure exactly how to add it in. Is there a way to do so? The KPI should display 11, but it is displaying 10 because I do not have my third column, which is a value of 1, included. If I can add that column in, it should display 11.

The second expression includes my third column, but is not counting as distinct. For my one example I am testing/validating with, the second expression should display 11 in the KPI. Right now, it is displaying 13, which means it is not distinctly counting the CSN's in the columns, but merely adding them all up, 5+1+7.

Is someone able to help me?

Thank you!

Re: Count Distinct Values from Multiple Columns in One KPI

marcus_sommer — Mon, 19 Apr 2021 16:02:39 GMT

You may try it in this way:

=Count(Distinct [ADM_CSN]&[OBV_ADM_CSN]&[IP_ADM_CSN])

- Marcus

Re: Count Distinct Values from Multiple Columns in One KPI

HelmickC — Mon, 19 Apr 2021 17:37:40 GMT

Hi Marcus,

Thank you so much for your prompt reply. I honestly cannot tell if your solution works or not since the KPI just keeps "calculating". I am not sure why, but it will not display a number at all. Do KPI's sometimes take super long to calculate the expression we enter in? When I revert it back and use my previous function, it works, but when I try to input yours, nothing...

Re: Count Distinct Values from Multiple Columns in One KPI

HelmickC — Mon, 19 Apr 2021 17:42:53 GMT

It finally stopped and I received this message.

Re: Count Distinct Values from Multiple Columns in One KPI

marcus_sommer — Tue, 20 Apr 2021 07:05:42 GMT

Usually it means that the datamodel is unsuitable for this kind of view respectively there are more or less serious gaps or mistakes. The heaviest part on a calculation is to build a virtual table of all included fields related the specific object - those within the dimensions and all from the expressions - which are needed to provide the dimensional context for the aggregations.

If now all of your measure fields come from different tables which may even associated through a link-table such virtual table could be become quite huge and may easily hit a timeout or a RAM limit.

Therefore I suggest to review your datamodel carefully and if it's not a star-scheme to change it in this direction.

- Marcus