8 Replies Latest reply on Nov 13, 2018 3:23 PM by Don Wise

    Date Difference by Month for Contract Days

    Cynthia Derksen

      I am trying to figure out how to calculate contract days on my report.  I need to know the total number of days per month that we have someone contracted.

       

      So, if someone contracted us to start on 1/1/2018 and the contract ends on 3/31/2018.  They would have an 90 day contract.

      With the next person starting on 2/1/2018 and ending on 3/17/2018; they would have 45 contract days.

       

      I need the total number of contract days per month. 

       

      Contract #JanFebMarchTotal
      #131283190
      #2 281745

       

      I have checked the the forum and I am not able to find anything that suit this scenario.  I am thinking it maybe a table calculation but I don't have much experience with it.

      We will be using the calculated totals by month for budget planning. 

        • 1. Re: Date Difference by Month for Contract Days
          Joe Oppelt

          Just do

           

          DATEDIFF('day', [Start Date], [End Date])

           

          You'll get the diff between the two dates in an integer value of days.

          • 2. Re: Date Difference by Month for Contract Days
            Joe Oppelt

            Are you saying that you actually need to list the specific number of days per each month?


            Tableau works on actual rows of data.  If you don't have a row on each date in each month that the contract was in effect, you won't have anything physical to count up.  In fact, if you even had one row per month you could hack up a series of calcs to compute the number of days for each month, but if you just have one row per contract to give the start- and end-date, you'll have a hard time getting what you're looking for.

            • 3. Re: Date Difference by Month for Contract Days
              Cynthia Derksen

              The date diff doesn't work because I need to know the number of days per month.

              If you look at my example it shows the total for unique_ID 9.

               

              1) What I should see across the top would be each month displayed from July 2017 to May 2018.

              2) Under each month from July 2017,  I should see 31 days August 31 days, Sept. 30 days, etc.

              3) I would then total the row and Columns.

               

              What I am thinking which I haven't been able to write a formula for is as follows:

               

              IF StartDate is Column month then datediff from end of month to Start days

              Else if StartDate month is Less than current month then days for month

              Else If endDate Month <> Current Month the Days for month;

              Else if end date is current month then days to end date.

               

              I  am not sure if this explains it.

               

              Here is an example of what I hope my report will look like.

              • 4. Re: Date Difference by Month for Contract Days
                Cynthia Derksen

                So, I was thinking that I will need to compare to days of the month.

                • 5. Re: Date Difference by Month for Contract Days
                  Don Wise

                  Hi Cynthia,

                  What you're looking to do will involve a process known as scaffolding (a support system) for your date spans.  Please see attached 10.3 workbook and below screenshots for how this is done.  It's fairly simple after joining a new table of data which is nothing more than a set of continuous dates from 01/01/2017-12/31/2018:

                   

                  If this resolves your question, please mark as correct. Thx! Don

                   

                  I brought in a new MS-Excel worksheet which is just a series of consecutive dates for a two-year period:

                  Screen Shot 2018-11-13 at 12.45.39 PM.png

                  Then did a Cartesian JOIN 1:1, using the very bottom of the drop down and simply type 1 on each side of the join, selecting INNER JOIN:

                  Screen Shot 2018-11-13 at 12.46.10 PM.png

                  Notice the conflation of the data.  This procedure works really well for small sets of time, but may cause a great deal of expansion if using larger sets of data/time:

                  Screen Shot 2018-11-13 at 12.48.14 PM.png

                  There's a new calculation which is a T or F filter brought into the Filter's card:

                   

                  [Calendar Date] >=[Start Date]

                  AND

                  [Calendar Date] <=[End Date]

                   

                  I then swapped out the Start Date from Columns with Calendar Date, then swapped out SUM of Contract Days with COUNT(Unique ID), then added grand totals for rows and for columns.

                  Screen Shot 2018-11-13 at 12.50.46 PM.png

                  1 of 1 people found this helpful
                  • 6. Re: Date Difference by Month for Contract Days
                    Egor Larin

                    Hey Cynthia,

                     

                    I did a little bit different than Don Wise BTW Don, where are the contracts without end dates?

                     

                    Indeed, we need master data, that is what Joe Oppelt mentioned. The idea is to have for every contract the dates where it is valid, for example Unique ID=9 we must have rows from 01/07/17 to 08/02/18 = 8 rows

                    I did the same as Don - added master data to your excel with dates until 2023. If you know SQL and data is in DB then it master data generation can be done with a query.

                     

                    Then I did the RIGHT JOIN calculation:
                         date(datetrunc('month',[Start Date])) <= Master Date

                    And then I did the viz:

                    1. Filtered out rows where Unique ID is null - rows from master data before min Start Date
                    2. Filtered out extra records for months after today() month. Just to make viz smaller and faster
                    3. Found the marks where End Date = Master Date (red color) just to see where it was ended. Orange is for Unique IDs with end date, blue (null) - Unique ID has not yet ended
                    4. Found the end of each Master Month - Max Day Month.
                      We will need it to calculate number of days for month where Unique ID is valid
                      date(DATEADD('day',-1,DATEADD('month',1,[Maste Date])))
                    5. Calculated duration in days - Duration
                      1. If Unique ID has 2 contracts then master dates will be duplicated, so I want to filter out them
                      2. If Start Date and End Date is in the same month - the duration is between Start Date and End Date
                      3. If id is valid then duration is from Start Date of to end of master for first month and
                      4. from the start of master to the end of master. The same logic if End Date is null

                        

                     

                    Hope that works! At least I've enjoyed solving it

                     

                    Egor

                     

                    PS

                    Issues

                    • Some Unique IDs are not really Unique
                      • Unique ID 8. It has not closed records and in the same time you have a records with closed. Not sure why, but I've closed it with 28/02/2018
                      • Unique ID 56. It has not closed range with closed row. I've deleted it
                      • Unique 392. Has the same issue as 56. I've deleted it
                    1 of 1 people found this helpful
                    • 7. Re: Date Difference by Month for Contract Days
                      Cynthia Derksen

                      Don,

                       

                      Thank you that is 1/2 of the puzzle.  That is picking up everyone that has an end date.   I also need the number of days for the group that haven't terminated the contact.

                       

                      So, I modified your date filter to include this and It looks correct. .

                       

                      [Calendar Date] >=[Start Date]

                      AND

                      ([Calendar Date] <=[End Date]

                      Or IF (ISNULL([End Date])) THEN

                      [Calendar Date]<=NOW()

                       

                      END)

                      • 8. Re: Date Difference by Month for Contract Days
                        Don Wise

                        Hi Cynthia,

                        That's easy enough to do; go to the filter and select NULL in addition to TRUE. Thx, Don

                         

                        Screen Shot 2018-11-13 at 3.22.45 PM.png