Count IF Distinct

[Anonymous]

Hello Everyone,

I'm new to Qlik Sense. I've been looking around on the forums unsuccessfully, and I am experiencing difficulty in my attempts to write an expression that does the equivalent of Count IF and Distinct.

In the table below, I have two columns, I would like to write an expression that counts the number of distinct vehicles that are false under the approved column.

Vehicle	Approved?
14-6105	TRUE
12-6407	TRUE
12-6407	TRUE
16-0405	FALSE
16-0405	FALSE
12-4232	FALSE

The answer should be 2.

How would i go about doing this?

I've tried writing something like this,

=count(distinct{<[Approved?]={'FALSE'}>}[Vehicle])

Please Help!

[Ivan_Bozov · Accepted solution]

Try like this: Count({<[Approved?]={'FALSE'}>}Distinct [Vehicle])

[msteedle]

Your expression does work. Try it:

Vehicle:

LOAD * INLINE [

    Vehicle, Approved?

    14-6105, TRUE

    12-6407, TRUE

    12-6407, TRUE

    16-0405, FALSE

    16-0405, FALSE

    12-4232, FALSE

];

count(distinct{<[Approved?]={'FALSE'}>}[Vehicle]) = 2

I would check that your real data is as clean as you believe it is (ex. no leading/trailing spaces) and has only one distinct Approved? value per Vehicle.

[Anonymous]

You're right, it does work; my only problem was that I should not have been looking for a "FALSE" return.

The excel sheet I use, marked it as TRUE or FALSE, but when I loaded the data and tried using a filter pane as a temporary work around, it did not display TRUE or FALSE, rather 0 and 1.

When I changed it to the following, I got the right answer:

=count(distinct{<[Approved?]={"0"}>}[Vehicle])

Do you know why this might have been the case?

[Anonymous]

Thanks for the help on this. It was helpful.

[msteedle]

Excel considers TRUE and FALSE to be binary values, not just the strings "TRUE" and "FALSE". It's just an artifact of Excel, although you do occasionally run into that when extracting data from some databases, I have found.

[janocete]

Thanks for your answer. It was very useful to me.