5 Replies Latest reply on Dec 6, 2018 9:30 AM by Norbert Maijoor

# Creating a New Calculated Field

Hello everyone,

I have some problem with creating the calculated field that I need to analyze my data. I have a data where I have individual name where each person may have different in cross and out cross dates and I shortly explain what I wanted to do in my analyzes. I want to calculate the length of visit per person per year.

At first I want to calculated how many people visited our country e.g in 2009 and now I want to see how long did they stay in my country but I want to take into account the one year period from the first visit if he/she has two visits in 2009. e.g. If someone has  different in cross and out cross dates e.g. someone has 3 in cross and out cross dates 1. in cross date 02-02-2009 out cross date 02-03-2009 so he stayed for 1 month but he/she also has another visit in cross date 02-05-2009 out cross date 02-07-2010 he stayed for 14 months and he also has one visit - in cross date 02-05-2011 out cross date 02-06-2011  he stayed for one month.

So as I want to calculate how many people visited in 2009 so I do not want to take into account the in cross and out cross date in 2011. As I look the length of visit for one year period from the first visit in 2009 so from the minimum visit date, I want to calculate how many days did this person stay in my country within this 1 year so for this example I should calculated how many days did this person stay from 02-02-2009 to 02-02-2010. As this person has two visits within this period because this person also visited my country on 05-05-2009, I should take into account the length of this visit but as I am looking at only the one year period from the first visit the length of the second visit should be shown as 9 months. (difference between the date of the second in cross date (05-05-2009) and the last date in one year period (02-02-2010)).  So in total I want to see that the length of visit of this person is 1 month plus 9 months so 11 months.

If the second out cross date was within this one year period I would calculate the difference between this out cross date and the corresponding in cross date.

Could you please tell me how to do it in tableau? I created the dateadd('day', 365, [in cross date]) but I want to created it as 365 days from the first visit for each year and I do not know how to combine all these thing together to do this analysis.

Thank you very much in advance,

Sincerely,

Nino

• ###### 1. Re: Creating a New Calculated Field

You're going to need to provide a sample packaged workbook that shows how your data is laid out.

• ###### 3. Re: Creating a New Calculated Field

Hi Nino,

Not sure;) but find my attempt based on your sample data as reference below and stored in attached workbook version 10.5 located in the original thread.

1. D1. Date1: {fixed [Individual Names]:min(dateadd('year',1,[In cross date]))}

2. D2. Date2: if [D1. Date1]<=[Out cross date] then [D1. Date1] else [Out cross date] END

3. M1. delta: DATEDIFF('day',[In cross date],[D2. Date2])

4. M2. Delta: if [D2. Date2]<=[In cross date] then 0 else [M1. delta] end

5. M3. Delta: {fixed [Individual Names]:sum([M2. Delta])}

Hope it helps.

Regards,

Norbert

1 of 1 people found this helpful
• ###### 4. Re: Creating a New Calculated Field

Thank you very much.It works. It really helped me.

Thank you!

• ###### 5. Re: Creating a New Calculated Field

Hi Nino,

You are welcome:) Glad is worked out for you. Please "close" this thread with a "Helpfull" or "Correct Answer" if that's the case;). The community will be aware and can focus on other challenges.