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

[Anonymous]

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

[swuehl]

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

[damian_spyra]

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]

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]

Thanks Damian, I'll try this at work tomorrow

cheers,

Pokes

[swuehl]

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]

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

Cheers,

Pokes

[damian_spyra]

Hi Pokes,

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

Have a wonderful day!

Damian

[Anonymous]

Hi Stefan,

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

cheers,

Pokes

[damian_spyra]

Hi Pokes,

can you paste your formula to show the syntax?

Thanks

Damian