# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

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

Hi All,

Here's my data:

 Employee Call Type Phone Number John Work 07100 123456 John Personal 07100 123456 John Work 07999 654321 Sarah Work 07123 456789 Sarah Work 07456 567890 Sarah Personal 07000 787878 John Work 07100 123456

The end result will look like this:

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

13 Replies
MVP

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

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

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

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

New Contributor II

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

Thanks Damian, I'll try this at work tomorrow

cheers,

Pokes

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

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

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

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

New Contributor II

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

Hi Pokes,

can you paste your formula to show the syntax?

Thanks

Damian