Count based on group condition

[#1]

Hi,

Say I have the following dataset:

ServiceNumber	Status	CloseDate
1234	Open
1234	Close	8 Jan 2015
3456	Open

I would like to get a count of all ServiceNumbers that are still open, and have not been closed. In this case, the answer should be 1 (for service number 3456). How can I do this in set analysis expression?

[robert_mika]

What if the Status was "Close"?

[tyagishaila]

If Status was close than at any time it was opened also.. Only open store can close..

Yes its not perfect way to count Service Number .. I just gave an idea..

[nikhilgarg · Accepted solution]

HEy,

Here it is . Try this and let me know:

Count(Aggr(Count(DISTINCT ServiceNumbers),ServiceNumbers)) - Count(Aggr(Count({<Status = {'Close'}>}DISTINCT ServiceNumbers), ServiceNumbers))

[#5]

Wow! This works perfectly thanks!

There's also another way that my colleague suggested:

count({<Status={'Open'}, ServiceNumber-=P({<Status={'Close'}>})>}ServiceNumber)

[nikhilgarg]

Hey,

But when i tried your given expression it is showing me error. IS it working fine in your case ?

count({<Status={'Open'}, ServiceNumber-=P({<Status={'Close'}>})>}ServiceNumber)

Thanks

[#7]

It does show a syntax error (red underline), but the expression calculates fine.

[nikhilgarg]

Hey,

Yeah i see to it . BTW can you tell me how the following function working?? I mean what does it ouputs ?

ServiceNumber-=P({<Status={'Close'}>})