Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
MVP
MVP

=Count({<

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

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

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


>} [Internal Audits.Audit No])

View solution in original post

Highlighted
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

Highlighted
MVP & Luminary
MVP & Luminary

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.



Highlighted
Creator II
Creator II

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

Highlighted
MVP
MVP

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

Highlighted
Specialist II
Specialist II

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

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

Highlighted
Creator II
Creator II

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