Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
samuelt1
New Contributor

Count IF Distinct

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.

VehicleApproved?
14-6105TRUE
12-6407TRUE
12-6407TRUE
16-0405FALSE
16-0405FALSE
12-4232FALSE

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!

1 Solution

Accepted Solutions
Highlighted
Luminary
Luminary

Re: Count IF Distinct

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

View solution in original post

6 Replies
Highlighted
Luminary
Luminary

Re: Count IF Distinct

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

View solution in original post

Highlighted
Partner
Partner

Re: Count IF Distinct

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.

Highlighted
samuelt1
New Contributor

Re: Count IF Distinct

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?

Highlighted
samuelt1
New Contributor

Re: Count IF Distinct

Thanks for the help on this. It was helpful.

Highlighted
Partner
Partner

Re: Count IF Distinct

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.

Highlighted
janocete
New Contributor

Re: Count IF Distinct

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