Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pokermon
New Contributor II

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

Re: Counting if .... is greater than AND...

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

phone numbers.png

13 Replies
MVP
MVP

Re: Counting if .... is greater than AND...

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

damian_spyra
New Contributor II

Re: Counting if .... is greater than AND...

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

pokermon
New Contributor II

Re: Counting if .... is greater than AND...

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

pokermon
New Contributor II

Re: Counting if .... is greater than AND...

Thanks Damian, I'll try this at work tomorrow

cheers,

Pokes

MVP
MVP

Re: Counting if .... is greater than AND...

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



pokermon
New Contributor II

Re: Counting if .... is greater than AND...

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

Cheers,


Pokes

damian_spyra
New Contributor II

Re: Counting if .... is greater than AND...

Hi Pokes,

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

Have a wonderful day!

Damian

pokermon
New Contributor II

Re: Counting if .... is greater than AND...

Hi Stefan,

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

cheers,

Pokes

damian_spyra
New Contributor II

Re: Counting if .... is greater than AND...

Hi Pokes,

can you paste your formula to show the syntax?

Thanks

Damian

Community Browser