11 Replies Latest reply on Nov 20, 2018 7:20 AM by Paul Wachtler

How to keep certain values from one field based on multiple conditions?

I have data that included individuals with different statuses. My goal is to only show individuals that have two specific statuses at the same time. In the example below, I only want to keep individuals that have a status of A AND C.

I tried a simple calculation:

(IF [Status] = 'A' and [Status] = 'C' then "both" else "other" END)

But it does not work.

I would appreciate any assistance.

Based on the table above, I would only want to show Frank Jones, James Carver,  and John Smith.

• 1. Re: How to keep certain values from one field based on multiple conditions?

Hi n s,

Here is the calculation you're looking for:

if {FIXED [Name]: count([Status])} > 1 then "both" else "other" end

Let me know if you have any questions.

Best,

Paul

• 2. Re: How to keep certain values from one field based on multiple conditions?

Hi Paul,

This works with the example I provided. My apologies for not providing data that was a more accurate representation of the data I am using. The solution you provided works if Status only has two possible values.

I need a solution that would pull out and group two specifics values for that one field when that field has multiple values. I have attached a new data and provided a screenshot below.

In this example I would only want to show the Names of people that have a Status of A AND C, so in this case it would be Frank Jones, James Carver and John Smith.

I have attached new workbook.

• 3. Re: How to keep certain values from one field based on multiple conditions?

In that case, you need this calculation:

if { FIXED [Name]: sum( if [Status] = "A" or [Status] = "C" then 1 end) } > 1 then "both" else "other" end

You're just updating the aggregation in the LOD to check if the person has both A and C.  If they do, the sum will be 2 or more and the calc will return the "both" value.

• 4. Re: How to keep certain values from one field based on multiple conditions?

Thanks Paul, this almost works. For some reason it's marking Anne Brooke as "both', when she is only A and B. See screenshot below:

• 5. Re: How to keep certain values from one field based on multiple conditions?

You have two rows for Anne Brooke in your data so it was counting the A status twice.  This calc will account for that and only count each Status once for each person.

if sum({ FIXED [Name]: countd( if [Status] = "A" or [Status] = "C" then [Status] end) }) > 1 then "both" else "other" end

• 6. Re: How to keep certain values from one field based on multiple conditions?

Yes! That worked! Thanks for all of your help!

• 7. Re: How to keep certain values from one field based on multiple conditions?

You're welcome!  If you can mark my answer as helpful as well, I'd appreciate it

• 8. Re: How to keep certain values from one field based on multiple conditions?

Could you help with one last piece?.... I need to show the total number of individuals that meet these conditions. In this example it would be showing the value "3" for the 3 people that have a Status of "A" and "C". I've tried a few things but I can't seem to get it to only show the the single total. I just want it to simply show "3" see screenshots below:

• 9. Re: How to keep certain values from one field based on multiple conditions?

For some reason I'm having a hard time getting the calculation right for that.  The easiest thing to do, on your view where you have your grand total - hold ctrl (or command on a Mac), select the three rows above it, right click, and select "hide".  Then you can right click on the Name pill on your Rows shelf and deselect "show header".

• 10. Re: How to keep certain values from one field based on multiple conditions?

That worked! Thanks for all of your help Paul!!

• 11. Re: How to keep certain values from one field based on multiple conditions?

You're welcome!