Set Expression with Multiple Conditions that need to be met.

[jbchurchill]

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

[Saravanan_Desingh]

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

Can u try this?

[Or]

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 · Accepted solution]

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]

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

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

[Or]

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