Qlik Sense KPI count value with distinct from another value

[Fedemar]

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!!

[dwforest · Accepted solution]

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

[Fedemar]

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?

[Fedemar · Accepted solution]

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!!