5 Replies Latest reply on Aug 14, 2018 7:05 AM by Nino Chikovani

# creating a new calculated field

Hello,

I have a data source like this. Person A had a Permanent residence, temporary residence and citizenship as well, B has only Permanent and temporary residences and C has only temporary residence but with different expiry dates. I want to visualize them like this: those who are like "A" should be reflected in "they got citizenship", those who are like B residence exp date should be only Null (the exp date of temporary residence should be ignored) and those who are like C only the residence exp date of the last temporary residence should be included. Could you please tell me how can I write this in a calculated field? I have Tableau 10.0.

Thank you very much,

Sincerely,

Nino

• ###### 1. Re: creating a new calculated field

Wow, that seems like a great deal to have in a single calculation. I want to structure the data differently! But I think it's doable. I'm assuming this is one calc field. Here's one possible way:

First the individual pieces. Person "A":

{FIXED [Name of Individual]: MIN(IF [Residence] = 'Permanent' AND [Citizenship] THEN 'they got citizenship' END)}

Person B:

{FIXED [Name of Individual]: MIN(IF [Residence] = 'Permanent' AND NOT [Citizenship] THEN 'NULL' END)}

Person C:

{FIXED [Name of Individual]: MAX(IF [Residence] = 'Temporary' THEN STR([Residence Expiry Date]) END)}

Combining these together:

IFNULL(

IFNULL(

{FIXED [Name of Individual]: MIN(IF [Residence] = 'Permanent' AND [Citizenship] THEN 'they got citizenship' END)}

, {FIXED [Name of Individual]: MIN(IF [Residence] = 'Permanent' AND NOT [Citizenship] THEN 'NULL' END)}

)

,{FIXED [Name of Individual]: MAX(IF [Residence] = 'Temporary' THEN STR([Residence Expiry Date]) END)}

)

(Okay, I cheated a bit on the second line in order to use the IFNULL.)

• ###### 2. Re: creating a new calculated field

Thank you very very much. It really helped.

• ###### 3. Re: creating a new calculated field

Everything worked well, but there is something that I noticed later. Later I checked whether this way gave me a correct answers or not and using simple filters - dragging corresponding columns on filter shelf - it appeared that e.g people who got permanent residence were fewer by about 40 people than it was shown by using the calculated field written above (number of those people who got citizenship was correct, but number of people who got permanent residence were different). Do you have any idea or explanation why this calculated field - the fixed function gave me a different results?

Thank you very much,

Sincerely,

Nino

• ###### 4. Re: creating a new calculated field

Good day Nino.

FIXED LOD expressions bypass normal filters. If you are filtering the data in other ways, the FIXED LOD counts can bypass those filters. If this is the case, change all the filters in your view to Context filters and see if the numbers match up with your test/check.

Tableau has an article on the order of operations here:

-Dan

• ###### 5. Re: creating a new calculated field

Thank you very much for your help.