6 Replies Latest reply on Nov 23, 2018 2:12 AM by kavi ram

    Date difference between two status for single ID

    kavi ram

      Hi ,

      I have data like below,

       

      I need to find the calculation for Response time in days where my status are 'Risk Approved' & 'Risk investigation'. here we need to consider both status of max date as 'Date declare' and 'Date valid' column.

      1) i have created max date calculation for status 'Risk investigation' as below,

      Risk investigation:

      Risk investigation_Date Declare:

      if [Status]='Risk investigation' then {Fixed [Id],[Status]: MAX([Date Declare])} end

       

      Risk investigation_Date valid:

      if [Date Declare] = [Risk investigation_Date Declare] THEN [Date valid] end

       

      2) i have created max date calculation for status 'Risk Approved' as below,

      Risk Approved:

      Risk Approved_Date Declare:

      if [Status]='Risk Approved' then {Fixed [Id],[Status]: MAX([Date Declare])} end

       

      Risk Approved_Date valid:

      if [Date Declare] = [Risk Approved_Date Declare] THEN [Date valid] end

       

      So based on the sample data above have the results for max date for both status. i am getting struct here to find 'Response Time (days)' as result expected.

      My final calculation as below, and i am getting 'Null' value instead of 18 d for Id 2 in the data above.

       

      Response Time (days):

      if DATEDIFF('day',[Risk investigation_Date valid],[Risk Approved_Date valid])=0 then 1

      else DATEDIFF('day',[Risk investigation_Date valid],[Risk Approved_Date valid])

      END

       

      Please help me to find the Response Time(days) calculation and the result should be as in data above.

       

      Thanks in advance.

      Regards,

      Kavi

       

      Lei Chen,

        • 1. Re: Date difference between two status for single ID
          Donna Coles

          Hi Kavi

           

          FYI - I have moved your post to the main Forums section of the site where it should get much more visibility.  Posting in the 'getting started' section of the community site, is more for questions on how to use the community site, than get help on specific data questions.

           

          Looking at what you've posted above, if the data is constructed as you're showing in the table, then the reason you're getting nulls in your Response Time calc is because that calculation will be looking at the value of the fields on a row by row basis, and there is no row where you have a value for both the [Risk investigation_Date valid] and the [Risk Approved_Date valid].  This data has been set against different rows for the same ID.

           

          To move forward with this, can I suggest you do the following

          1) Indicate exactly what output you're after - is it a table like the above with response time set against a single 'Risk Approved' row? or is it ID and response time?

          2) Provide a packaged workbook with the data and what you've done so far, so someone who chooses to help you out hasn't got to manually recreate everything. (see here : Packaged workbooks: when, why, how )

           

          Thanks

          Donna

          • 2. Re: Date difference between two status for single ID
            suresh.gooty

            Simple Sir,

             

            create one more formula and write below

            [Risk investigation_Date valid] - [Risk Approved_Date valid]

             

            if you want round it to remove decimals... good luck

            • 3. Re: Date difference between two status for single ID
              kavi ram

              Hi DONNA COLES,

               

              Thanks for you kind reply, I Need out put like below,

              For example Id 2,

              Id     Status                            Date Declare          Date valid                 Expected Response time

              2     Quotation                        12-10-2018             12-10-2018                  Null

                     Risk Approved                 09-11-2018             09-11-2018                 18 d  

                     Risk investigation            19-10-2018             22-10-2018                 Null

               

              Hope you will understand my requirement. and i have attached the workbook also.

               

              Thanks in advance,

              Kavi

              • 4. Re: Date difference between two status for single ID
                kavi ram

                Hi Suresh,

                 

                As both the status date has different rows, so direct subtract still will give null value for all the records,

                 

                Thanks for you kind reply.

                Regards,

                Kavi

                • 5. Re: Date difference between two status for single ID
                  Donna Coles

                  Hi Kavi

                   

                  Thanks for posting the workbook.

                   

                  I have used table calculations to populate the dates you want to work with across all the rows for a single ID.  I have then been able to compare the difference on a single row then, and only output a result if the status is 'Risk Approved'.

                   

                  So I have a new calc field

                  [Risk_Invest_Date Valid Per ID] = WINDOW_MAX(MAX([Risk investigation_Date valid]))

                  When added to the view, its set so that it is partitioned by ID

                  which has the effect of putting the data from the 1 row you originally calculated, across all the rows for the ID, like below

                   

                  I did the same for

                  [Risk_Approve_Date Valid Per ID] = WINDOW_MAX(MAX([Risk Approved_Date valid]))

                   

                  and set the same Table Calc settings.

                   

                  I could then write a new [response time] field, that outputs the diff between these 2 dates, but only if the status is the one you want

                  [Response Time (days) v2] =

                  IF ATTR([Status]) = 'Risk Approved' THEN

                      IF DATEDIFF('day', [Risk_Invest_Date Valid Per ID], [Risk_Approve_Date Valid Per ID]) = 0 THEN 1

                      ELSE DATEDIFF('day', [Risk_Invest_Date Valid Per ID], [Risk_Approve_Date Valid Per ID])

                      END

                  END

                   

                  NOTE - the table calc settings of this field must also match the above

                   

                   

                  Hope that helps.

                   

                  Solution attached in v10.5

                  Regards

                  Donna

                  • 6. Re: Date difference between two status for single ID
                    kavi ram

                    Hi Donna,

                     

                    Thank you very much!!!..