Set Analysis 'or' condition

[jmonroe918]

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

1. Status does not = Closed     AND
2. Sheduled Date is after today (> Today)   OR
3. 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

[chiru_thota]

Re: OR operator in set analysis

[bgerchikov · Accepted solution]

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.

[qlikpahadi07]

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!!!

[sunilkumarqv]

Try this

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

[jagan]

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.

[jonathandienst]

Hi

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

[CELAMBARASAN]

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

[jmonroe918]

Thanks for your help, Boris.

Jeff