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,
Training Sample Data.xlsx 10.6 KB