Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Counting if .... is greater than AND...

Hi All,

Here's my data:

  

EmployeeCall TypePhone Number
JohnWork07100 123456
JohnPersonal07100 123456
JohnWork07999 654321
SarahWork07123 456789
SarahWork07456 567890
SarahPersonal07000 787878
JohnWork07100 123456

The end result will look like this:

Screen Shot 2017-10-09 at 21.32.25.png

So what I'm trying to calculate is:

show me the phone number that has been used for both work and personal calls (and only these numbers)

I tried something like this as the measure function in a bar chart count(call type = 'work'>0 AND call type = 'Personal'>0) - obviously this doesn't work - please can someone provide a formula to do this??

thanks,

Pokes

1 Solution

Accepted Solutions
Not applicable
Author

Working
COUNT({<[Phone Number]={"=COUNT(DISTINCT([Call Type]))=2"}>}[Phone Number])

phone numbers.png

View solution in original post

13 Replies
swuehl
MVP
MVP

Count({<PhoneNumber = {"=Count(DISTINCT CallType)=2"}>} PhoneNumber)

damian_spyra
Contributor III
Contributor III

Hi Pokes,

you could use kind of additional flag field like "_work" and "_personal".

Then you can evaluate:      if( _work * _personal, Phone Number)

Cheers

Damian

Anonymous
Not applicable
Author

Hi Stefan,

Would you mind explaining this please, what does the 2 in the formula represent? Is it a phone number that matches both phone call types?

many thanks,

Pokes

Anonymous
Not applicable
Author

Thanks Damian, I'll try this at work tomorrow

cheers,

Pokes

swuehl
MVP
MVP

This part in the expression is called Set Analysis:

{<PhoneNumber = {"=Count(DISTINCT CallType)=2"}>}


A Primer on Set Analysis

It's basically filtering your PhoneNumber by using an expression search:

Only include the number in the set to aggregate where the distinct count of CallType values is 2, i.e. both Work and Private calls have been made (assuming that these are the only existing types).



Anonymous
Not applicable
Author

Ok, i got it. Many thanks - I'l try this at work tomorrow too.

Cheers,


Pokes

damian_spyra
Contributor III
Contributor III

Hi Pokes,

I think, Stefans solution is the best fit for your needs.

Have a wonderful day!

Damian

Anonymous
Not applicable
Author

Hi Stefan,

Tried your formula at work today, unfortunately it gave an 'error in expression' warning. Any ideas??

cheers,

Pokes

damian_spyra
Contributor III
Contributor III

Hi Pokes,

can you paste your formula to show the syntax?

Thanks

Damian