8 Replies Latest reply: Oct 28, 2015 5:28 AM by Mayank Raoka

# Qlikview Count Distinct, with and IF OR satement

I want to count the distinct drivers that start an account job OR complete a street job. I know to count the distinct drivers (using set analysis) who start an account job is:

count(DISTINCT

{\$

<_account_jobs_started={1}>

}

driverId)

and to count the number of drivers who do a street job is

count(DISTINCT

{\$

<_street_job={1}>

}

driverId)

But how do I do this as an OR statement if I do:

count(DISTINCT

{\$

<_street_job={1},_account_jobs_started={1}>

}

driverId)

This will make it an IF AND statement.

How do I do an IF OR count. Your help would be much appreciated.

• ###### Re: Qlikview Count Distinct, with and IF OR satement

bump..

One way would be to use IF itself..

count(DISTINCT

if(_street_job=1 or _account_jobs_started=1,driverId)

)

but I think the more robust solution would be to make this a field in the script. something like

if(_street_job=1 or _account_jobs_started=1,1,0) as Flag

and you just use set analysis <Flag={'1'}>.

• ###### Re: Qlikview Count Distinct, with and IF OR satement

I think, using  + for union of sets

count(DISTINCT

{\$

<_account_jobs_started={1}>

}

+

{\$

<_street_job={1}>

}

driverId)

count(DISTINCT

{\$

<_account_jobs_started={1}>

+

<_street_job={1}>

}

driverId)

• ###### Re: Qlikview Count Distinct, with and IF OR satement

Great! thanks!

Quoting QV Help:

Several set operators that can be used in set expressions exist. All set operators use sets as operands, as described above, and return a set as result.

+ Union. This binary operation returns a set consisting of the records that belong to any of the two set operands.

- Exclusion. This binary operation returns a set of the records that belong to the first but not the other of the two set operands. Also, when used as a unary operator, it returns the complement set.

* Intersection. This binary operation returns a set consisting of the records that belong to both of the two set operands.

/ Symmetric difference (XOR). This binary operation returns a set consisting of the records that belong to either, but not both of the two set operands.

The order of precedence is 1) Unary minus (complement), 2) Intersection and Symmetric difference, and 3) Union and Exclusion. Within a group, the expression is evaluated from left to right. Alternative orders can be defined by standard brackets, which may be necessary since the set operators do not commute, e.g. A+(B-C) is different from (A+B)-C which in turn is different from (A-C)+B.

• ###### Re: Qlikview Count Distinct, with and IF OR satement

Set Analysis: syntaxes, examples

and......

• ###### Re: Qlikview Count Distinct, with and IF OR satement

Thank you this solves my issue as well

• ###### Re: Qlikview Count Distinct, with and IF OR satement

=if(_street_job=1 or _account_jobs_started=1,count(distinct driverId))

or

=count(distinct if((_street_job=1 or _account_jobs_started=1, driverId))

• ###### Re: Qlikview Count Distinct, with and IF OR satement

Hi,

this should do the trick:

count(DISTINCT {\$<_street_job={1}> + <_account_jobs_started={1}>} driverId)

regards

• ###### Re: Qlikview Count Distinct, with and IF OR satement

Thanks!!! That Worked.

Is there a way to plot a chart directly from the Pivot table ? Right now I have to recreate the measures ?

Is there an efficient way to do the pivot and aggregations in excel ? Not everyone in my team uses QlikView.

Thanks again.