Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count based on group condition

Hi,

Say I have the following dataset:

ServiceNumberStatusCloseDate

1234

Open
1234Close8 Jan 2015
3456Open

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?

16 Replies
robert_mika
Master III
Master III

What if the Status was "Close"?

tyagishaila
Specialist
Specialist

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
Specialist II
Specialist II

HEy,

Here it is . Try this and let me know:

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

Not applicable
Author

Wow! This works perfectly thanks!

There's also another way that my colleague suggested:

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

nikhilgarg
Specialist II
Specialist II

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

Not applicable
Author

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

nikhilgarg
Specialist II
Specialist II

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'}>})