Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

What if the Status was "Close"?

Highlighted
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..

Highlighted
Specialist
Specialist

HEy,

Here it is . Try this and let me know:

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

View solution in original post

Highlighted
Not applicable

Wow! This works perfectly thanks!

There's also another way that my colleague suggested:

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

Highlighted
Specialist
Specialist

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

Highlighted
Not applicable

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

Highlighted
Specialist
Specialist

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