Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

8 Replies
Not applicable

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

MVP
MVP

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)

Not applicable

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.

MVP
MVP

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

Not applicable

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

Partner
Partner

Hi,

this should do the trick:

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



regards

Not applicable

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.

Specialist
Specialist

Thank you this solves my issue as well