6 Replies Latest reply on Jun 13, 2017 3:52 AM by Mubasher Shah

    Tableau 10.0- Period between Start date and End date to be added under Each month

    Mubasher Shah

      I have a table with a start date and End date and now need to work out in tableau how to count the numbers of days between each period that falls within each month. I have attached a data sheet as sample data.

       

        • 1. Re: Tableau 10.0- Period between Start date and End date to be added under Each month
          Joe Oppelt

          I don't understand what you are doing with the yellow date, but you can find the difference between two dates with the DATEDIFF function.

           

          This is a valuable page that tells you all about all the date functions:

           

           

           

          Date Functions

           

          DATEDIFF(date_part, date1, date2).  date_part is a string like 'day', or 'year', or 'hour' to tell Tableau what units you want the return value.

           

          Just plug in your start and end fields, and you'll get your number of days.  From there you can to whatever math you need to do your yellow columns.

          • 2. Re: Tableau 10.0- Period between Start date and End date to be added under Each month
            Mubasher Shah

            Thanks Joe,

             

            Just to clarify I currently do not have the yellow fields in my data- it is only for reference.

             

            I am trying to calculate and create a chart for the number of days between start_date and end_date by each month. So when I select April I want to see only days for the month of April (17days) and May(5 days) but currently tableau picks up the start date into months and I can plot this but when I show April it shows 18days so includes the extra day of May.

             

            I did try to use the datediff calculation you recommended but it doesn't work for me to what I want. I want to create a chart like below in tableau.

             

            • 3. Re: Tableau 10.0- Period between Start date and End date to be added under Each month
              Joe Oppelt

              What happens when a range spans more than two months.  (Start date = January 15.  End Date = October 1.)

               

              Also, in your last post you said, "When I select April"...  For Start Date?

               

              Here is what has to happen.  You will need 12 measure calcs -- one for each month.  You'll have two dimension calcs:

               

              MONTH([Start Date])

               

              and

               

              MONTH([End Date])

               

              If the two values are the same, then the DATEDIFF value will just get placed in the associated measure calc for the month in question.  (All other values for the other 11 calcs will be zero.)

               

              If the two values are not the same, then based on the DAY portion of the date you will load up the load up the measure calc for the start month with the number of days remaining in that month, and you'll load up the measure calc for the end month based on the DAY value of the end date.  And if it spans multiple months, you'll load up the measure calcs for the months in between with the number of days for that month.  (And there is a way to deal with leap year for February if you need to.)

               

              It will be a little tedious to set it all up, but that's what you will need to do.  If you need help, get as far as you can with it, and post your workbook showing where you get stuck.

              • 4. Re: Tableau 10.0- Period between Start date and End date to be added under Each month
                Rajesh G

                April column:

                IF DATEDIFF('month',[Start Date],[End Date]) =0

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

                ELSE DATEDIFF('day',[Start Date], DATEADD('day',-1,  MAKEDATE(YEAR([Start Date]), MONTH([Start Date])+1,1)))

                END

                 

                May Column:

                IF DATEDIFF('month',[Start Date],[End Date]) >0

                THEN DATEDIFF('day',DATETRUNC('month',[End Date]),[End Date])+1

                END

                date.JPG

                • 5. Re: Tableau 10.0- Period between Start date and End date to be added under Each month
                  Mubasher Shah

                  Hello Rajesh,

                   

                  Firstly thank you for your help. Your solution works perfect for me but I have one issue now- I am pulling data from Microsoft SQL Server and the MAKEDATE is not recognised. Also is it possible to convert the month columns to a year column instead and change the field type to date so when you press on the date pill it expands to months rather than creating a column for each month manually? Just thinking of making it easier to update.