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

    lea.bajacan

      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
      2013-12-252014-01-05Canada
      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
          Shawn Wallwork

          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
            Jennifer VonHagel

            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
              lea.bajacan

              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
                lea.bajacan

                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
                  Jennifer VonHagel

                  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)