Count if

[clondono]

Hello community,

I need to calculate the number of requests that have gone through two selected steps.  There are a total of 8 steps in the lifecycle and a request may go through some of the steps but not trough other steps. 

What I need is the total count of distinct requests that at a given time have gone through "both" selected steps and their status is closed.

I'm using:

     Count({$<STATUS = {'Closed'}>}DISTINCT REQUEST_ID)

but this expression gives me the count of requests that have been in either step. For example if I filter by step 3 and 4 for FY16, it should return a count of 1 and show only May-2016 since there is only one request (#8216) that have been in both step3 and step4.  Instead it is returning the count of all steps that have been in one or the other step.

Any help is appreciated!

Carlos

[sunny_talwar · Accepted solution]

May be try this:

Count({$<REQUEST_ID = {"=Count(STEP) = GetSelectedCount(STEP)"}>}DISTINCT REQUEST_ID)

[clondono]

Thank you Sunny.  It looks correct, I will validate and let you know!

[sunny_talwar]

Sure thing

[clondono]

Yes, that was the perfect solution. 

Thanks Sunny!

[sunny_talwar]

Awesome