Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jbchurchill
Contributor III
Contributor III

Set Expression with Multiple Conditions that need to be met.

I'm struggling with this 2-part (dual condition) Set Expression. It is returning 0 which isn't what I wanted to see because I know filtering the same data in excel I currently get 6510 records.  I don't really know how to combine the 2 expressions which independently seem to do what I want. I think it should be an "AND" type of relationship because in my mind the logic is that I want ... a count of records past the expiration date that have not yet been completed.

=Count({<[expdate]={"<$(=Today())"}>+1-<[step_status]={'Completed'}>}[service_id])

 

Labels (1)
1 Solution

Accepted Solutions
jbchurchill
Contributor III
Contributor III
Author

I ended up solving it by including the other categories rather than trying to not include the completed category. Saran7de's answer gave me "-" (my profile pic currently) and Or's answer gave me "0".

=Count({<[expdate]={"<$(=Today( 0))"}, [step_status]={'Active', 'Queued'}>}[service_id])

View solution in original post

5 Replies
Saravanan_Desingh

=Count({<[expdate]={"<$(=Today())"}>}+{1-<[step_status]={'Completed'}>}[service_id])

Can u try this?

Or
Master III
Master III

Perhaps something along the lines of:

=Count({<[expdate]={"<$(=Today()+1)"}, [step_status]={'Completed'} >}[service_id])

The +1 probably should be inside the evaluated syntax, and the two conditions are typically separated by a comma (separating them with a + is an OR rather than AND, iirc)

jbchurchill
Contributor III
Contributor III
Author

I ended up solving it by including the other categories rather than trying to not include the completed category. Saran7de's answer gave me "-" (my profile pic currently) and Or's answer gave me "0".

=Count({<[expdate]={"<$(=Today( 0))"}, [step_status]={'Active', 'Queued'}>}[service_id])
manoranjan_d
Specialist
Specialist

if you want to exclude the completed status try the below exp

=Count({<[expdate]={"<$(=Today())"}>+1,[step_status]-={'Completed'}>}[service_id])
Or
Master III
Master III

@jbchurchillSorry, missed that you wanted to *exclude* Completed. That would be the same condition, except -={'Completed'} (minus equals) instead of just the equal sign.  I read the +1 as being (Today()+1) instead of Today() (+ 1-...). Glad you got it working, in any case.