13 Replies Latest reply: Oct 10, 2017 6:39 PM by Lok Mikklineni

# 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

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

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

• ###### 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

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

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

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

Cheers,

Pokes

• ###### 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

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

Hi Damian,

Yeah it looks like a good solution, unfortunately its showing an 'error in expression' alert - any ideas?

cheers,

Pokes

• ###### 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

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

Hi Pokes,

can you paste your formula to show the syntax?

Thanks

Damian

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

Damian,

I'm using the formula exactly as suggested by Stefan...

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

not too familiar with set analysis so not sure how / what to amend.

cheers,

Pokes

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

Double check that field names are spelled correctly (and take care of upper / lower case).

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

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

• ###### 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

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

Thanks Damian, I'll try this at work tomorrow

cheers,

Pokes