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 )
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
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,
Response Time (days).twbx 40.9 KB
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.
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])
NOTE - the table calc settings of this field must also match the above
Hope that helps.
Solution attached in v10.5
Response Time (days).twbx 66.1 KB
Thank you very much!!!..