Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
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)

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

Highlighted

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.