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

# Date difference between two status for single ID

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.

Regards,

Kavi

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

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

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

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.

Kavi

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

Hi Suresh,

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

Regards,

Kavi

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

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

Hi Donna,

Thank you very much!!!..