8 Replies Latest reply on May 2, 2018 8:50 AM by Okechukwu Ossai

Non exclusive if statement

I am in urgent need of formula help. Googling efforts did not pay off. I will post to the tableau site, but hoping to get a quick response. Any assistance is appreciated. I am using Desktop 10.3.1

I need a NON EXCLUSIVE calculated field. I think it is a nested field somehow, but not sure how.

 ID 17850 At RiskPast Due ID 89037 Past DueExtended ID 30654 At RiskDue in 60 days ID 39067 New

I want a field (a filter at the end of the day) that will give me the IDs that are CONTAIN Past Due, New, At Risk ext

So At Risk = 2

Past Due = 2

Extended = 1

New = 1

If I do

if contains([ID], ‘At Risk’) then 1

Elseif contains([ID],’Past Due’) then 1 etc

I get At Risk = 2

Past Due = 1  etc

It will pull the 2 At Risk, but the IDs that have Past Due and Due in 60 days will not get put in a bucket, The IDs will appear in multiple buckets at the end of the day.

• 1. Re: Non exclusive if statement

Hi Jessica,

How may fields do you have. How is your table structured. It is not very clear from the image you posted. Do you have 2 fields? [ID] and [Status]?

Assuming the first column in the image is a field called [ID] and second column is a field called [Status]

Then create a calculated field [ID Count]

{FIXED [Status]: COUNTD([ID])}

Drag [Status] to rows shelf and ID Count to text labels marks or columns shelf as a measure

Ossai

• 2. Re: Non exclusive if statement

I am not sure I was clear about what I am looking for. I have attached a very simple representation. In my current workbook I have an exclusive calculation that will find the FIRST in the String column. But what I want is for an ID to show when AGED or AT RISK is chosen. Example : should show when Aged or Due in 60 days is chosen in the filter.

Thanks.

• 3. Re: Non exclusive if statement

Oh...just realised you added an attachment. I'd take a look and get back to you

• 4. Re: Non exclusive if statement

Hi Jessica,

I think your [Exclusive Filter] calculation is correct except for one typo in the 'Due in 60 Days' condition. Try the modification below and see if it is what you are looking for.

IF CONTAINS([String],'Past Due') then 'Past Due'

ELSEIF  CONTAINS([String],'Aged') then 'Aged'

ELSEIF  CONTAINS([String],'At Risk') then 'At Risk'

ELSEIF  CONTAINS([String],'Due in 60 Days') then 'Due in 60 Days'

ELSE 'Other'

END

Regards,

Ossai

• 5. Re: Non exclusive if statement

Hi, Jessica

Below is steps

1st, join your data to a list of all your string as shown below

2nd, create a filter to filter data

An updated workbook attached. Hope this hleps

ZZ

• 6. Re: Non exclusive if statement

Hi Jessica,

I have looked at your dataset closely and now understand what the issue. The Exclusive Filter field will not work. Your data needs to be disaggregated. For example, Age needs to be selected before 'Due in 60 Days' can show where the two strings appear together. I'm not sure if there is a non-database workaround. I'll check to see if any other thing can be done but it's likely pointing towards the direction of disaggregating the records in the original data source outside Tableau.

Ossai

• 7. Re: Non exclusive if statement

Just an update - we got this figured out and it was apparently TOO easy:

• Created a parameter with list of the search strings
• Created a calculated field that does the “if contains” and uses the parameter.. returns value of TRUE if there’s a match
• Filtered the sheet to use that calculated field = TRUE
• 8. Re: Non exclusive if statement

Thanks for the update. Good you found a solution.