topic Set Analysis 'or' condition in QlikView

Set Analysis 'or' condition

jmonroe918 — Tue, 18 Mar 2014 00:16:28 GMT

I am trying to get a count of records that meet the following criteria:

Status does not = Closed AND
Sheduled Date is after today (> Today) OR
Intended Completion is after after today (> Today).

Below is my fomula but I'm getting an error and can't figure out why..

=Count(Distinct{<
[Internal Audits.Status]-={'Closed'},
[Internal Audits.Sched Date]={'>$(=Date(Today(),'MM/DD/YYYY'))'}+
[Internal Audits.Intended Completion Date]={'>$(=Date(Today(),'MM/DD/YYYY'))'}
>}[Internal Audits.Audit No])

Jeff

Re: Set Analysis 'or' condition

chiru_thota — Tue, 18 Mar 2014 03:52:39 GMT

Re: OR operator in set analysis

Re: Set Analysis 'or' condition

bgerchikov — Tue, 18 Mar 2014 04:14:45 GMT

Hi Jeffrey,

I think you have a problem due to syntactic error: you need to use double quotes instead of singles since set analysis returns more than one result.

Try this one

=Count({<[Internal Audits.Status]-={'Closed'},[Internal Audits.Sched Date]={">$(=Date(Today(),'MM/DD/YYYY'))"}>

<[Internal Audits.Status]-={'Closed'},[Internal Audits.Intended Completion Date]={">$(=Date(Today(),'MM/DD/YYYY'))"}>}

Distinct [Internal Audits.Audit No])

Hope it will help.

Re: Set Analysis 'or' condition

qlikpahadi07 — Tue, 18 Mar 2014 05:00:01 GMT

Hi Jeffrey

In Qlikview 'OR' is tricky

=Count(Distinct{<

[Internal Audits.Status]-={'Closed'},

[Internal Audits.Sched Date]={'>$(=Date(Today(),'MM/DD/YYYY'))'}

>}[Internal Audits.Audit No])

Count(Distinct{<

[Internal Audits.Status]-={'Closed'},

[Internal Audits.Intended Completion Date]={'>$(=Date(Today(),'MM/DD/YYYY'))'}

>}[Internal Audits.Audit No])

hope this Help

Cheers!!!

Re: Set Analysis 'or' condition

sunilkumarqv — Tue, 18 Mar 2014 05:04:41 GMT

Try this

=count({<[Internal Audits.Status]-={'Closed'},[Internal Audits.Intended Completion Date] = {"$(= '>=' & date(Today(),'MM/DD/YYYY')"}>} DISTINCT [Internal Audits.Audit No]))

Re: Set Analysis 'or' condition

jagan — Tue, 18 Mar 2014 05:17:08 GMT

Hi,

Try like this

=Count({<

[Internal Audits.Status]-={'Closed'},

[Internal Audits.Sched Date]={">$(=Date(Today(),'MM/DD/YYYY'))"}> +

<[Internal Audits.Status]-={'Closed'}, [Internal Audits.Intended Completion Date]={">$(=Date(Today(),'MM/DD/YYYY'))"}

>} Distinct [Internal Audits.Audit No])

Regards,

Jagan.

Re: Set Analysis 'or' condition

jonathandienst — Tue, 18 Mar 2014 05:26:56 GMT

Just one update to Jagan's post, the outer quotes in the date comparisons need to be double quotes, so that there is no 'collision' with the inner quotes:

=Count({

<[Internal Audits.Status]-={'Closed'}, [Internal Audits.Sched Date]={">$(=Date(Today(),'MM/DD/YYYY'))"}>+

<[Internal Audits.Intended Completion Date]={">$(=Date(Today(),'MM/DD/YYYY'))"}>

} Distinct [Internal Audits.Audit No])

HTH

Jonathan

Re: Set Analysis 'or' condition

CELAMBARASAN — Tue, 18 Mar 2014 05:38:13 GMT

By using expression Jagan Mohan post, still you will see syntax error. Don't bother about it, there is an issue with syntax checker for "-=" operator.

=Count({<

[Internal Audits.Status]-={'Closed'},

[Internal Audits.Sched Date]={">$(=Date(Today(),'MM/DD/YYYY'))"}> +

<[Internal Audits.Status]-={'Closed'}, [Internal Audits.Intended Completion Date]={">$(=Date(Today(),'MM/DD/YYYY'))"}

>} Distinct [Internal Audits.Audit No])

Re: Set Analysis 'or' condition

jmonroe918 — Tue, 18 Mar 2014 13:51:30 GMT

Thanks for your help, Boris.

Jeff