Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
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?

1 Solution

Accepted Solutions
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

16 Replies
Highlighted
Specialist II
Specialist II

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

Highlighted
Master III
Master III

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

Highlighted
Not applicable

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

Highlighted
Master III
Master III

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

Highlighted
Specialist
Specialist

HEy,

Try this:

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

Thanks

Highlighted
Not applicable

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

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

Highlighted

Try this:

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

Highlighted
Specialist
Specialist

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