Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
This question may have been covered off elsewhere in the forums before however i wasn't sure where to even start searching.
I have a history data set with the following columns:
The RAG_Status column can have a value of Red, Amber, Green or Gray.
The Return_To_Green_Date will only be populated if the RAG status is adverse (i.e. Red or Amber).
The history file is appended on a weekly basis to add new data - in this way a project will build up a history of its movements between RAG status'.
i need to perform the following calculations:
data set example:
Data_Captured_Date | Project_Name | RAG_Status | Return_To_Green_Date |
06/05/19 | Project A | RED | 15/05/19 |
13/05/19 | Project A | RED | 15/05/19 |
20/05/19 | Project A | GREEN |
|
27/05/19 | Project A | AMBER | 05/06/19 |
03/06/19 | Project A | RED | 05/06/19 |
10/06/19 | Project A | RED | 20/06/19 |
03/06/19 | Project B | AMBER | 25/06/19 |
10/06/19 | Project B | AMBER | 25/06/19 |
03/06/19 | Project C | AMBER | 25/06/19 |
10/06/19 | Project C | GREEN |
|
If the logic works correctly the following should be true:
If anybody has any ideas how I should go about formulating these calculations i would really appreciate it!
Thanks
Hi,
If scripting is an option you can use Peek to set a counter that resets on new project/non red/amber status. Not sure I follow your thing on the dates, but think you would do similar (but need to incorporate the date). I've pasted script below as I have personal edition, here is the result (I've added a flag to show last date by project;
Let me know if this helps (and is right/wrong) and I can have a think about the date change.
Cheers,
Chris.
Script
data:
LOAD * INLINE [
Data_Captured_Date, Project_Name, RAG_Status, Return_To_Green_Date
06/05/2019, Project A, RED, 15/05/2019
13/05/2019, Project A, RED, 15/05/2019
20/05/2019, Project A, GREEN,
27/05/2019, Project A, AMBER, 05/06/2019
03/06/2019, Project A, RED, 05/06/2019
10/06/2019, Project A, RED, 20/06/2019
03/06/2019, Project B, AMBER, 25/06/2019
10/06/2019, Project B, AMBER, 25/06/2019
03/06/2019, Project C, AMBER, 25/06/2019
10/06/2019, Project C, GREEN,
];
data_final:
NoConcatenate
Load
If(RowNo()=1,
If(Match(RAG_Status,'RED','AMBER')>0,1,0),
If(Peek(Project_Name)<>Project_Name,
If(Match(RAG_Status,'RED','AMBER')>0,1,0),
If(Match(RAG_Status,'RED','AMBER')>0,Peek([Adverse Period])+1,0)
)
) AS [Adverse Period],
*
Resident data
Order by Project_Name, Data_Captured_Date;
Left Join (data_final)
Load
Max(Data_Captured_Date) AS Data_Captured_Date,
Project_Name,
1 AS Max_Date
Resident data_final
Group by Project_Name;
DROP TABLE data;