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?

[sunilkumarqv]

Count(Distinct{<Status={'Open'}>} ServeiceNumber)

[arulsettu]

count({<Status={'Open'}>}ServiceNumber)

[#4]

This gives 2. I only want to count the Service Number that has not been closed.

[arulsettu]

based on the data you have two open servicenumbers 1234,3456 so the result is 2

[nikhilgarg]

HEy,

Try this:

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

Thanks

[#7]

This can be a work around, however is there a way I can actually group it by the SR number while counting? Because there may be some Service Numbers that are immediately classified as 'Closed' without a matching 'Open', so this will skew the numbers.

[nikhilgarg]

Ok i will let you know. I  m working on this....

[robert_mika]

Try this:

if(Count({<Status = {'Close'},CloseDate-={"=len(trim(CloseDate))=0}"}>}ServiceNumber)-Count({<Status = {'Open'}>}ServiceNumber)=-1,1,0)

[tyagishaila]

you can count only that ServiceNumber which comes only one time in your table.