4 Replies Latest reply on Sep 6, 2018 7:03 AM by Imran Azam

    Length of contract between start and end date  calculation field help

    Imran Azam

      Hi Guys

       

      i am created a report which has a start of contract date and end of contract date and i need to find out the length of contract in months.

       

      the rule i want to set is this

       

      If the end date month and year  = start date month year than length should be 1

      if the date difference between  start date   and end date is <1 then length should be 1

      IF the DAY of end date is 15 days more than the DAY of start date then count this as an extra month

      else number of months between start date and end date.

       

      i have attached a a tableau workbook with three date my current formula ( length of contract) does not do the above, on the workbook below is what it show be

       

      start date : 25/06/2017  enddate: 07/07/2018 should show 11 months mine is showing 12

      start date : 02/03/2018  enddate: 05/03/2018 should show 1 months mine is showing 0

      start date : 02/03/2018  enddate: 05/03/2018 should show 2 months mine is showing 2 ( this one is coming out correct

       

       

      on excel i created the below formula which worked for what i want

      =IF(Enddate=startdate,IF(DATEDIF(startdate,enddate,"m")<1,1,DATEDIF(startdate,enddate+15,"m")))

       

      but i don't know how i can create this into tableau

       

      can anyone help?

       

      thank you

       

       

            

      Start Date End Date
      25/06/201807/06/2019