4 Replies Latest reply on Nov 2, 2018 3:27 AM by Vertika Singh

    DATEDIFF help

    gareth.penpraze

      I have a workbook where I want to calculate the DATEDIFF of 2 dates from 2 separate records. My data goes a little like this:

       

      Col 1: ID

      Col 2: Current Status

      Col 3: Updated Status

      Col 4: Modified Time

       

      There are further columns of data within the data source which is why I haven't included the workbook. An example of some of the records go:

       

      IDCurrent StatusUpdated StatusModified date
      123Status AStatus A01/01/2019
      123Status BStatus B05/01/2019
      123Status CStatus C07/01/2019

       

      For this example I want to calculate the difference in days for ID 123  between Current Status = Status A and Updated Status = Status B.

       

      I just can't figure out how to do the Date diff on values in other dimensions. Any help is appreciated.

       

      Thanks,

      Gareth

        • 1. Re: DATEDIFF help
          Ankit Bansal

          Gareth,

           

          You can do this with LOD:

           

          {fixed [ID] : max(if [Current Status]='Status B' then [Modified date] end)-max(if [Current Status]='Status A' then [Modified date] end)}

           

           

          Thanks,

          AB

          1 of 1 people found this helpful
          • 2. Re: DATEDIFF help
            Cédric Tran

            Hi Gareth,

             

            To compare your Status Dates, you have to put them on the same line, here is a way to do it:

            1. Create a calculated dimension following this exemple:

            IF  [Status] = 'Status A'

                then { FIXED [ID] : MIN( IF  [Update] = 'Status B' THEN [Date] end ) }

            end

             

            I replied to the example you gave us, but if there are other functional logic behind, you have to fulfill the calculation with other cases.

             

            2. Create the calculation with the DattDiff function.

             

            Regards,

            Cédric

            1 of 1 people found this helpful
            • 3. Re: DATEDIFF help
              Mahfooj Khan

              Hi,

               

              Try this,

              DATEDIFF

                  ('day',

                      {FIXED [ID]:MIN(IIF([Current Status]="Status A",[Modified Date],NULL))},

                      {FIXED [ID]:MIN(IIF([Updated Status]="Status B",[Modified Date],NULL))}

                  )

              Let us know if this help.

               

              Mahfooj

              1 of 1 people found this helpful
              • 4. Re: DATEDIFF help
                Vertika Singh

                IF [Updated Status]='Status B' THEN

                DATEDIFF('day',

                {fixed [ID] : min(if [Updated Status]='Status A' then [Date] END)},

                {fixed [ID] : min(if [Updated Status]='Status B' then [Date] end)})

                elseif [Updated Status]='Status C' THEN

                DATEDIFF('day',

                {fixed [ID] : min(if [Updated Status]='Status B' then [Date] END)},

                {fixed [ID] : min(if [Updated Status]='Status C' then [Date] end)}) END