6 Replies Latest reply: Apr 25, 2014 8:42 AM by rolando gutierrez RSS

    day month year shows two dates qlikview

      Hello:

      I need to get the time work by an employee. In years, months, and days.

      having a start date and end date.

      use the following code and it did not work.

      examplo:

      end date:18/04/2014

      start date: 15/06/2010

      result:

      years:3

      months:10

      days:3

       

       

      IF

      ([End Date] <= AddYears([Start Date],1),'0 Years ',
      Age([End Date],[Start Date]) & ' Years ')
      &
      IF(MONTH([End Date]) = MONTH([Start Date]),'0 Months ',
      IF(DAY([Start Date]) < DAY([End Date]), fabs(MONTH([End Date])-Month([Start Date])) & ' Months ',
      ((MONTH([End Date])+12)-Month([Start Date]))-1  & ' Months '
      ))
      &
      IF(MONTH([End Date]) = MONTH([Start Date]),DAY([End Date]) - DAY([Start Date]) & ' Days',
      IF(DAY([End Date]) > DAY([Start Date]),DAY([End Date]) - DAY([Start Date]) & ' Days',
      DAY(DATE(Interval([End Date] - [Start Date])))-1 & ' Days'
      ))

       

      helpme

      please

      rolando

        • Re: day month year shows two dates qlikview
          Stefan Wühl

          Maybe like this:

           

          Set DateFormat = 'DD/MM/YYYY';

           

           

          LOAD *,


          Age([End Date],[Start Date]) & ' Years '

          &

          IF(month([Start Date]) < month([End Date]),

            mod((month([End Date]) - month([Start Date])) + (Day([Start Date]) > Day([End Date])),12)  & ' Months ',

            mod(12-(month([Start Date]) - month([End Date])) + (Day([Start Date]) > Day([End Date])),12)  & ' Months ' )

          &

          if(Day([End Date])> Day([Start Date]), Day([End Date])-Day([Start Date]) & ' Days', day(monthend([Start Date]))-day([Start Date])+day([End Date]) & ' Days') as Diff;


          LOAD * INLINE [

          End Date, Start Date

          18/04/2014, 15/06/2010

          18/06/2014, 15/06/2010

          18/04/2014, 15/02/2010

          8/06/2014, 15/06/2010

          31/12/2014, 01/01/2013

          01/01/2014, 31/12/2013

          ];

          • Re: day month year shows two dates qlikview
            Stefan Wühl

            There is small issue in the Days calculation, where we need to use a larger equal comparison in the if clause:

             

            LOAD *,


            Age([End Date],[Start Date]) & ' Years '

            &

            IF(month([Start Date]) < month([End Date]),

              mod((month([End Date]) - month([Start Date])) + (Day([Start Date]) > Day([End Date])),12)  & ' Months ',

              mod(12-(month([Start Date]) - month([End Date])) + (Day([Start Date]) > Day([End Date])),12)  & ' Months ' )

            &

            if(Day([End Date])>= Day([Start Date]), Day([End Date])-Day([Start Date]) & ' Days', day(monthend([Start Date]))-day([Start Date])+day([End Date]) & ' Days') as Diff;


            • Re: day month year shows two dates qlikview
              Stefan Wühl

              Maybe using the age() function:

               

              =age('15/04/2014','30/04/2006')

                • Re: day month year shows two dates qlikview

                  The function age does not give me the exact years,

                   

                  examplo:

                  =age('15/04/2014','30/04/2006')

                  We obtain: 7 years  with several months.

                  I need a function that I have fulfilled the seven years:

                  For examplo:

                  Of 1 to 7 years this age('15/04/2014','30/04/2006') not qualify,he have more time.

                  This Qualify age('15/04/2013','30/04/2006') 6 years with several months.

                   

                  I need an exact function of completed years, not more

                   

                  help me