topic Re: Count based on group condition in QlikView

Count based on group condition

Mon, 19 Jan 2015 09:03:10 GMT

Hi,

Say I have the following dataset:

ServiceNumber	Status	CloseDate
1234	Open
1234	Close	8 Jan 2015
3456	Open

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?

Re: Count based on group condition

sunilkumarqv — Mon, 19 Jan 2015 09:17:09 GMT

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

Re: Count based on group condition

arulsettu — Mon, 19 Jan 2015 09:18:36 GMT

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

Re: Count based on group condition

Mon, 19 Jan 2015 09:19:53 GMT

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

Re: Count based on group condition

arulsettu — Mon, 19 Jan 2015 09:22:38 GMT

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

Re: Count based on group condition

nikhilgarg — Mon, 19 Jan 2015 09:23:17 GMT

HEy,

Try this:

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

Thanks

Re: Count based on group condition

Mon, 19 Jan 2015 09:43:04 GMT

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.

Re: Count based on group condition

nikhilgarg — Mon, 19 Jan 2015 10:17:24 GMT

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

Re: Count based on group condition

robert_mika — Mon, 19 Jan 2015 11:15:21 GMT

Try this:

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

Re: Count based on group condition

tyagishaila — Mon, 19 Jan 2015 11:35:22 GMT

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

Re: Count based on group condition

robert_mika — Mon, 19 Jan 2015 11:40:59 GMT

What if the Status was "Close"?

Re: Count based on group condition

tyagishaila — Mon, 19 Jan 2015 12:01:10 GMT

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

Re: Count based on group condition

nikhilgarg — Mon, 19 Jan 2015 12:21:53 GMT

HEy,

Here it is . Try this and let me know:

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

Re: Count based on group condition

Tue, 20 Jan 2015 01:55:16 GMT

Wow! This works perfectly thanks!

There's also another way that my colleague suggested:

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

Re: Count based on group condition

nikhilgarg — Tue, 20 Jan 2015 08:37:30 GMT

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

Re: Count based on group condition

Tue, 20 Jan 2015 08:43:01 GMT

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

Re: Count based on group condition

nikhilgarg — Tue, 20 Jan 2015 08:51:55 GMT

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