Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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?

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Re: Count based on group condition

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

16 Replies
Highlighted
Specialist II
Specialist II

Re: Count based on group condition

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

Highlighted
Master III
Master III

Re: Count based on group condition

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

Highlighted
Not applicable

Re: Count based on group condition

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

Highlighted
Master III
Master III

Re: Count based on group condition

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

Highlighted
Specialist
Specialist

Re: Count based on group condition

HEy,

Try this:

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

Thanks

Highlighted
Not applicable

Re: Count based on group condition

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.

Highlighted
Specialist
Specialist

Re: Count based on group condition

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

Re: Count based on group condition

Try this:

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

Highlighted
Specialist
Specialist

Re: Count based on group condition

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