countif on historic data with a stop condition

[chrismtb]

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:

• Data_Captured_Date
• Project_Name
• RAG_Status
• Return_To_Green_Date

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:

• Number of previous weeks that the project has been adverse (Red or Amber). However if at any point the RAG status moves to Green then the counter resets to 0.
• Number of times that the Return_To_Green_Date changes within an adverse period

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:

• Project A has been adverse (Red Or Amber RAG_Status) for the last 3 weeks
• Project A's Return_To_Green_Date has changed 1 time within that 3 week period
• Project B has been adverse for (Red Or Amber RAG_Status) for the last 2 weeks
• Project B's Return_To_Green_Date has changed 0 times within that 2 week period
• Project C has been adverse (Red Or Amber RAG_Status) for 0 weeks

If anybody has any ideas how I should go about formulating these calculations i would really appreciate it!

Thanks

[chrismarlow]

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;