topic Re: Set Analysis - Multiple Criteria (And) in QlikView

Set Analysis - Multiple Criteria (And)

jmonroe918 — Fri, 28 Feb 2014 07:24:38 GMT

I want to count the number of audit that meet the following criteria:

Status is not "Closed"
Class is "Internal"
Intended Completion (date) is "less than Today (Now)"

My current expression is as follows:

=Count({<[Internal Audits.Status]-={'Closed'}, [Internal Audits.Class]={'Internal'}>} [Internal Audits.Audit No])

How do I include the part where the Intended Completion date is less than today's (or current) date?

Jeff

Re: Set Analysis - Multiple Criteria (And)

MK_QSL — Fri, 28 Feb 2014 07:30:21 GMT

=Count({<

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

[Internal Audits.Class]={'Internal'},

[Intended Completion] = {'<$(=Today())'}

>} [Internal Audits.Audit No])

Re: Set Analysis - Multiple Criteria (And)

er_mohit — Fri, 28 Feb 2014 07:32:39 GMT

Try this

Count({<[Internal Audits.Status]-={'Closed'}* [Internal Audits.Class]={'Internal'}*

CompletionDate={'<$(=date(today()))'}>} [Internal Audits.Audit No])

check out the format of CompletionDate is same as Date(Today()) format

hope it helps

Re: Set Analysis - Multiple Criteria (And)

jagan — Fri, 28 Feb 2014 07:42:10 GMT

Hi,

Try this

=Count({<[Internal Audits.Status]-={'Closed'}, [Internal Audits.Class]={'Internal'},

CompletedDate={'<$(=Today())'}>} [Internal Audits.Audit No])

Replace CompletedDate with your dimension name. If it is not working then check date formats of CompletedDate values and Today() are in the same format, if not then convert the format of Today() by using

Date(Today(), 'MM/DD/YYYY') - Replace MM/DD/YYYY with your date format.

Regards,

jagan.

Re: Set Analysis - Multiple Criteria (And)

jmonroe918 — Fri, 28 Feb 2014 07:50:57 GMT

Jagan:

The exact expression I have there now is:

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

When I execute the number I get doesn't match the number I get when I run the qurey in MS Access (where my data is). I get 247 and it should be 6.

Jeff

Re: Set Analysis - Multiple Criteria (And)

tresB — Fri, 28 Feb 2014 08:18:22 GMT

Remove one closing parenthesis from : 'MM/DD/YYYY')))' and make it : 'MM/DD/YYYY'))'

Re: Set Analysis - Multiple Criteria (And)

preminqlik — Fri, 28 Feb 2014 08:43:20 GMT

i think you have to add Distinct over there....add it...

Re: Set Analysis - Multiple Criteria (And)

MK_QSL — Fri, 28 Feb 2014 09:20:30 GMT

Try This....

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

If still not working, we request you to upload your sample file....

Re: Set Analysis - Multiple Criteria (And)

jmonroe918 — Fri, 28 Feb 2014 15:11:46 GMT

I figured out the problem. I had the incorrect field named called out. It is actually "Intended Completion Date".

I appreciate the help from all of you, and I still needed the help with adding that part to the expression.

Thanks to all of you.

Jeff