4 Replies Latest reply: Oct 17, 2017 12:40 PM by RAVI SHINGE RSS

    Need to compare two dates columns

    RAVI SHINGE

      Hello Guys , I have two columns of Actual Date and Forecast dates . I need to compare these two columns and need to find the difference between these two dates . Could you please help me how to do this . How do I show difference ?

      Actual dateForecast Dates
      12/12/201601/09/2016

       

      I need one more column to show that whats the difference by how many (days, months , years)  between actual and forecast

       

      Thanks In Advance,

      ravi

        • Re: Need to compare two dates columns
          Felip Drechsler

          Hi Ravi,


          During the load script, use the Interval function.

          As an example, I did something like the bellow script:

           

          x:

          load * Inline

          [

          Actual date, Forecast Dates

          12/12/2016, 01/09/2015

          ];

           

          date:

          Load

          *,

          Interval([Actual date]-[Forecast Dates],'d') as [Difference Days],

          floor(Interval([Actual date]-[Forecast Dates],'d')/30) as [Difference Months],

          floor(Interval([Actual date]-[Forecast Dates],'d')/30/12) as [Difference Years]

          Resident x;

           

          drop table x;

           

          For your data, it would give the following

           

          sample.png

           

          Felipe.

            • Re: Need to compare two dates columns
              RAVI SHINGE

              Thanks a lot for the detailed expression !

               

              My Another question is as follows:-

               

              I have a Date column and I want to check if there are any null values in the Date column . And if there are null values in the date column then I want to put today's date and subtract it with forecast dates .

              How should this be done .

               

              Thanks,

              ravi shinge

                • Re: Need to compare two dates columns
                  Felip Drechsler

                  Hi Ravi,

                   

                  Try something like this:

                   

                  x:

                  load * Inline

                  [

                  Actual date, Forecast Dates

                  12/12/2016, 01/09/2015

                  ,01/05/2014

                  ];

                   

                  date:

                  Load

                  *,

                  Interval([Actual date]-[Forecast Dates],'d') as [Difference Days],

                  floor(Interval([Actual date]-[Forecast Dates],'d')/30) as [Difference Months],

                  floor(Interval([Actual date]-[Forecast Dates],'d')/30/12) as [Difference Years];

                  Load

                  // Gets the nulls with today date

                  if(len([Actual date])>0,[Actual date],date(Today(),'MM/DD/YYYY')) as [Actual date],

                  [Forecast Dates]

                  Resident x;

                   

                  drop table x;

                    • Re: Need to compare two dates columns
                      RAVI SHINGE

                      Thanks again for instant reply !

                       

                      I am writing this as an expression as I am creating dimension or so called column to represent these date differences .

                       

                      So now if i write this as an expression to create a dimension  .

                       

                      So now I wrote IF condition  but how to write then condition  :-

                      For instance I write below code in the expression :-  

                       

                      if(len([Actual date])>0,[Actual date],date(Today(),'MM/DD/YYYY'))     ------- upto here I am fine as I am checking IF condition  but after this Again I want to Subtract    Today's date with Forecast dates 

                      so how should i write here in the expression .How do I connect IF condition and below mentioned code

                       

                      Ex:- Interval [Today date - [Forecast date ]] ,'d')

                       

                      Thanks,

                      Ravi