2 Replies Latest reply on Nov 26, 2014 5:51 AM by Gabriel Piche

# Mutually exclusive calculated fields

Hello,

I am doing some text analysis and I faced a problem with the calculated fields. What I want to do is to take the comments and to extract concepts that can be found in those comments. To do so I created calculated fields that looks like the following:

IF(INT(

CONTAINS([Clean Comment], ' rate ')

or CONTAINS([Clean Comment], ' rates ')

or CONTAINS([Clean Comment], ' expensive')

or CONTAINS([Clean Comment], ' fee ')

or CONTAINS([Clean Comment], ' charge')

or CONTAINS([Clean Comment], ' charging')

or CONTAINS([Clean Comment], ' afford')

or CONTAINS([Clean Comment], ' cost')

or CONTAINS([Clean Comment], ' cheap')

or CONTAINS([Clean Comment], ' price')

or CONTAINS([Clean Comment], ' prices')

or CONTAINS([Clean Comment], ' pricing')

)>0 )

THEN 'Price'

END

I have 15 of those that I would like to combine together in order to show the counts of each concepts in a chart.

My 1st attempt was to take all those calculated field and merge them into a big one in order to be able to see the different concepts.

I also tried doing the following calculation:

IF[Site Function]='Site Function'

THEN 'Site Function'

ELSE IF  [Price]='Price'

THEN 'Price'

END

Both ways work but the issue that I get is that the comments are mutually exclusive. What I mean is that once a comment have been classified as a specific concept it won't go through the other rules to see if It would <fit> in another concept. I NEED the concepts to not be mutually exclusive like they are at the moment and I couldn't find how.

I am sorry I can't post my workbook because I am working client data. If needed I can make a mock-up.

Thanks,

Gabriel

• ###### 1. Re: Mutually exclusive calculated fields

Gabriel,

Rather than having a single field determine the category of the comment, it seems like you might want to consider using multiple measure fields.

PriceCountA = Price criteria set 1 ... THEN 1 ELSE 0 END

PriceCountB = Price criteria set 2 ... THEN 1 ELSE 0 END

OtherCategory = Price criteria set x ... THEN 1 ELSE 0 END

Then, you can sum each of the different measures and have a count for each criteria.

You could combine the PriceCountA + PriceCountB as TotalPriceCount or IF PriceCountA = 1 OR PriceCountB = 1 THEN 1 ELSE 0 END...

• ###### 2. Re: Mutually exclusive calculated fields

I should have though of doing it this way!

Thanks a lot Mark!