Personally, I would write the CONTAINS function the other way, as in CONTAINS([JFF Type], "search string"), but that's not going to fix your problem.
To help debug, I would create a new sheet that shows [Emp Flag], [Category1], [JFF Type] and [Workforce Category] - is there a trailing space ? are all the upper/lower case correct ?, etc. ...
But to answer your other question, there is no limit to the number of ELSEIFs you can have in your formula.
Hi Chris thank you so much for taking your time to look at my question. I will double check my formulas to make sure there are no issues with spacing or case etc.
However, I am not sure what do you mean by creating a new sheet? I do have a dashboard where I have used these fields as filters to get to the desired category. But I wanted to create this new field so that I can display all categories in one go.
Ah sorry, I didn't explain myself well .... when I'm debugging calculated fields that aren't working correctly I create another sheet to show all the steps.
A simpler example is X = A+B/C
I've done it, and I think everyone has done it, where you "know" it's going to work properly, but it doesn't .. maybe in just a handful of cases.
I create a new sheet, with A, B, C and X and then you can walk through all the scenarios that Tableau sees to try to understand where the answer is wrong.
In your case you can filter the new sheet down to Category Other because that's where it's not working properly. Hopefully this helps you to find the error quicker, and then you just delete the sheet because it's only there as a debugging tool
Actually Chris, rearranging those parameters could very well fix the problem, if we make the assumption that [JFF Type] could contain more content than what is being searched.
If [JFF Type] = "IT JFF, Project JFF, FY 2017"
CONTAINS ([JFF Type], "IT JFF, Project JFF") = TRUE
CONTAINS ("IT JFF, Project JFF", [JFF Type]) = FALSE
1 of 1 people found this helpful
Others have already indicated, you can have as many ELSEIF blocks as you need. So something else is causing the remaining checks to fail.
Here's a completely different way to approach the problem, to avoid having to create massive IF/ELSE statements:
1. Create a Combined Field out of the 3 fields that you are checking.
2. Create a Group Field out of the Combined Field. You can create "groups of 1" to bucket everything separately (or lump stuff together if they get the same Workforce Category), and then rename the groups to whatever you want. It's kind of like Aliases, but for constructs that don't support editing aliases. (EDIT: Actually, you can specify aliases on a Combined Field — so you wouldn't need the Group Field — but you have to put the field onto the shelves before you can Edit Aliases. You can't do it from the Field List on the left.)
Here's an example of creating a combined field and group for Segment and Category from the Superstore. I came up with group names, using the "Rename" button.
Thanks Jamieson it worked perfectly fine and much easier than writing the formulas.
Creating a group isn't the same as your original question though. A group can't do the "contains" logic that you had in your initial.
If the group works for you then great , but it's not what you were asking for at the start.
Sameer, glad it worked for you!
Chris, you're absolutely right that the group doesn't behave exactly the same as the original IF block. I threw the group idea out there because I began to suspect that the CONTAINS() wasn't actually needed — that it was being used where a straight equality was sought. Worked out in this case.