5 Replies Latest reply on Oct 1, 2017 8:06 AM by Jennifer VonHagel

# Count Number of Days Per Year When From and To Date Spans Across Multiple Years

Hi! My data set looks like the one below. I am trying to determine the sum of the number of days stayed in Country within a given year.

FROM DATETO DATECOUNTRY
2013-12-202013-12-25United States
2014-01-052014-01-10Germany

My problem here is for the second row which runs from 2013 to 2014. What I've done so far is to use the FROM DATE (year) as my Columns and create a calculated field as per below:

IF

DATEPART('year', [From Date]) = DATEPART('year', [To Date]) THEN [Num Days]

ELSEIF

DATEPART('year', [From Date]) <> DATEPART('year', [To Date])

THEN MAKEDATE(DATEPART('year', [From Date]), 12, 31) - [From Date]

END

This calculates the number of days for 2013 correctly. However, the number of days from Jan 1 - 5, 2014 is getting dropped, since my 2014 data will be looking at rows where FROM DATE (year) is 2014.

What is the best way to do this?

Note: I don't use Tableau professionally. I am working on a personal project at the moment after attending a 2-day intro workshop last week. My apologies in advanced if this question sounds very basic.

Thanks a lot!

• ###### 1. Re: Count Number of Days Per Year When From and To Date Spans Across Multiple Years

Try this:

FLOAT([To Date]) - FLOAT([From Date])

That should give you the correct number of days from any date to any other date. (Unless you get pre-first century.)

--Shawn

• ###### 2. Re: Count Number of Days Per Year When From and To Date Spans Across Multiple Years

Hi Lea, your dates here only span 2013 and 2014. If these are the only two years possible, or you know specifically which years are possible, then you could create yearly counts like the following. It's VERY manual and hard-coded, but: 2013 Days

//From and To in 2013

IF YEAR([From Date]) = 2013 AND YEAR([To Date]) = 2013

THEN DATEDIFF('day',[From Date],[To Date])+1

//From 2013, To 2014

ELSEIF YEAR([From Date]) = 2013 AND YEAR([To Date]) = 2014

THEN DATEDIFF('day',[From Date],#12/31/2013#)+1

//From 2012, To 2013

ELSEIF YEAR([From Date]) = 2012 AND YEAR([To Date]) = 2013

THEN DATEDIFF('day',#1/1/2013#,[To Date])+1

ELSE 0

END

2014 Days

//From and To in 2014

IF YEAR([From Date]) = 2014 AND YEAR([To Date]) = 2014

THEN DATEDIFF('day',[From Date],[To Date])+1

//From 2014, To 2015

ELSEIF YEAR([From Date]) = 2014 AND YEAR([To Date]) = 2015

THEN DATEDIFF('day',[From Date],#12/31/2014#)+1

//From 2013, To 2014

ELSEIF YEAR([From Date]) = 2013 AND YEAR([To Date]) = 2014

THEN DATEDIFF('day',#1/1/2014#,[To Date])+1

ELSE 0

END

• ###### 3. Re: Count Number of Days Per Year When From and To Date Spans Across Multiple Years

Thanks Shawn! I re-phrased my question to make it more clear.

• ###### 4. Re: Count Number of Days Per Year When From and To Date Spans Across Multiple Years

Thanks Jennifer! I've toyed at the idea of doing this way but wondering if there is a more "sophisticated" way of doing this. My actual data set spans across multiple years.

• ###### 5. Re: Count Number of Days Per Year When From and To Date Spans Across Multiple Years

Hmmm, to get a Year as a dimension across which you can count days, you would have to actually add rows to your data set. You could do that - you could union your entire file to itself and label one file as FROM and the other as TO, and then create calculations. Or you could try to union only those records where From Year <> To Year.  Something like this. by the way the calcs here are just to give you an idea of what I'm going for; they're not syntax-correct. Otherwise, I think you will have to manually create Annual Measures - one for each of the years in your data set.  I've attached a workbook which shows some calculations which are slightly less hard-coded than the last version .

From Year Days:

IF [From Year] = [To Year]

THEN DATEDIFF('day',[From],[To])+1

ELSEIF [From Year] < [To Year]

THEN DATEDIFF('day',[From],MAKEDATE([From Year],12,31))+1

ELSE 0

END

To Year Days:

IF [From Year] = [To Year]

THEN 0

ELSEIF [From Year] < [To Year]

THEN DATEDIFF('day',MAKEDATE([To Year],1,1),[To])+1

ELSE 0

END

2013 Days:

SUM(IF [From Year] = 2013 THEN [From Year Days] ELSE 0 END) +

SUM(IF [To Year] = 2013 THEN [To Year Days] ELSE 0 END)

2014 Days:

SUM(IF [From Year] = 2014 THEN [From Year Days] ELSE 0 END) +

SUM(IF [To Year] = 2014 THEN [To Year Days] ELSE 0 END)

20xx Days:

SUM(IF [From Year] = 20xx THEN [From Year Days] ELSE 0 END) +

SUM(IF [To Year] = 20xx THEN [To Year Days] ELSE 0 END) 