Count Distinct if sum result is less than X

[youss123]

Hi All,

Please help me out correcting my set analysis expression,

I want to Count Tickets  when the Sum of duration of Statut  "New" + "Assignment" is less than 6 days 

Knowing that : 

= Sum({<Statut ={"New","Assignment"}  >} [Statut_Duration]) 

Calculate the desired duration.

& this is how I count my  tickets

= Count( distinct {<Statut ={"New","Assignment"} >} [%Key - Ticket])

Thanks,

Y

[GaryGiles · Accepted solution]

Try this: 

= Count( distinct {<[%Key - Ticket]={"=Sum({<Statut ={'New','Assignment'}  >} [Statut_Duration])<6"},Statut ={'New','Assignment'} >} [%Key - Ticket]) 

[youss123]

Thank you Sir 

[youss123]

Another small question please , is it possible to count the number of tickets over a range of duration i.e. greater than and less than.

Thank you 

Y

[GaryGiles]

In this case, you would need to write it like this:

= Count( distinct {<[%Key - Ticket]={"=Sum({<Statut ={'New','Assignment'}  >} [Statut_Duration])<6 and Sum({<Statut ={'New','Assignment'}  >} [Statut_Duration])>2"},Statut ={'New','Assignment'} >} [%Key - Ticket])