6 Replies Latest reply on Jan 20, 2017 3:04 AM by Richard Silvekroon

# Formula for differentiate Male/ Female based on ID

Hi,

I'm new to tableau and to this forum and was wondering if someone who has more experience using formulas in tableau would be able to give me a few pointers.

I wish to create a filter where I can differentiate between Male / Female, I have a data source with personal numbers (similar to social security numbers) in this format: 8406083936 where the 9th number indicate male or female. Odd number = male, even number= female. In Excel I would use something like this:

=IF(ISODD(MID(A1;8;1));"Male";"Female")

but I'm unfamiliar with the way Tableau formulas are structured and work and I have no developer background so my capacity is a bit limited in this regard. I would greatly appreciate any assist in this matter.

• ###### 1. Re: Formula for differentiate Male/ Female based on ID

hi Richard,

So the structure of the formula is pretty similar (in fact Tableau has greater flexibility on the syntax)...we don't, however, have an ISODD function, but not to worry

To use the Excel style we need to use IIF (notice the extra I)...although Tableau also understands IF THEN ELSEIF ELSE END and also CASE statements.

So (and I'm assuming your social security number is already a string) you can try

IIF(INT(MID(A1,8,1))%2=0,"Female","Male")

the % is the MOD function, so returns the remainder from a division, so if a number%2 = 0 then its even

I've not used the calculation editor so can't guarantee I've got all the brackets in the right place!! but hopefully you get the general idea

Let me know if this doesn't do the trick, and I'll work up an example for you.

1 of 1 people found this helpful
• ###### 2. Re: Formula for differentiate Male/ Female based on ID

Hi Richard, here's a similar approach using the MOD function just like Simon did, with a separate calculated field to pick out the nth digit (the attached example is based on six-digit ID numbers with the fifth digit indicating gender):

INT(MID(STR([ID]),5,1)) will pick out the 5th digit.

IF [Last Digit]%2 = 0 THEN "Female" ELSE "Male" END will indicate gender.

-- Glenn

2 of 2 people found this helpful
• ###### 3. Re: Formula for differentiate Male/ Female based on ID

Nice work Glenn...good to show the other form of IF in Tableau, and a workbook example (to prove it works!)

• ###### 4. Re: Formula for differentiate Male/ Female based on ID

Thanks Simon.

I find your solution more elegant, although I personally do like having the second calculated field so that i can check my work, at least as an intermediate step.

-- Glenn

• ###### 5. Re: Formula for differentiate Male/ Female based on ID

Thank you for the swift and helpful response!

• ###### 6. Re: Formula for differentiate Male/ Female based on ID

Hi Glenn,

Thank you for the help and for providing a workbook example, it helped a great deal to understand the underlying structure!

//Richard