Qlikview Count Distinct, with and IF OR satement

[#1]

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.

[#2]

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'}>.

[maxgro]

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)

[#4]

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.

[maxgro]

for set analysis read this

Set Analysis: syntaxes, examples

and......

Please mark helpful and correct answers so others can find solutions to their problems too

[#6]

=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))

[jaimeaguilar]

Hi,

this should do the trick:

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

regards

[#8]

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.

[mayankraoka]

Thank you this solves my issue as well