Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jmonroe918
Creator II
Creator II

Set Analysis - Multiple Criteria (And)

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

  1. Status is not "Closed"
  2. Class is "Internal"
  3. 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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

=Count({<

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

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

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


>} [Internal Audits.Audit No])

View solution in original post

8 Replies
MK_QSL
MVP
MVP

=Count({<

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

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

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


>} [Internal Audits.Audit No])

er_mohit
Master II
Master II

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

jagan
Luminary Alumni
Luminary Alumni

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.



jmonroe918
Creator II
Creator II
Author

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

tresesco
MVP
MVP

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

preminqlik
Specialist II
Specialist II

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

MK_QSL
MVP
MVP

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

jmonroe918
Creator II
Creator II
Author

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