topic Qlik Sense KPI count value with distinct from another value in App Development

Qlik Sense KPI count value with distinct from another value

Fedemar — Fri, 25 Jan 2019 17:47:31 GMT

Hello everyone!

I'm in need of the community! I've made a pie chart that is specified like this:

Dimensions:

=ExceptionType

Measures:

Count(distinct [CustomerName])

This shows me how many types of exceptions there were in a period without counting twice an exception to the same Customer

An example of a table would be:

ID||ExceptionType||CustomerName

1||Management||John

2||Management||John

3||Employee||Rob

4||Sales||Sue

5||Sales||Bob

The chart shows me 1 Management exception, 1 Employee Exception and 2 Sales exceptions totalling 4 exceptions.

I need to create a KPI that is the total of what is shown in the chart. I'm sure it's somehow easy but couldn't figure it out.

Count(ExceptionType) shows me 5 exceptions total (2 from Management when it should be 1 because it's a duplicate entry).

Thanks!!

Re: Qlik Sense KPI count value with distinct from another value

dwforest — Fri, 25 Jan 2019 18:34:14 GMT

Sum(Aggr(Count(distinct [CustomerName]),ExceptionType))

Re: Qlik Sense KPI count value with distinct from another value

Fedemar — Fri, 25 Jan 2019 19:07:04 GMT

I'm sorry but doesn't seem to be giving me the number I'm looking for. Could it be because there are some ID's that have the Exception Type as null? Is there any way to exclude ExceptionType null values? Perhaps with set analysis on the ExceptionType part?

Re: Qlik Sense KPI count value with distinct from another value

Fedemar — Fri, 25 Jan 2019 19:14:48 GMT

Dwforest,

I tried what you wrote previously but it opened a little more my mind given you thought it the same way. I added a set expression to filter null values and it worked! I'm posting the solution here:

Sum(Aggr(Count({<[ExceptionType]-={"","NULL"}>}distinct [CustomerName]),[ExceptionType]))

So thanks!!