countif on historic data with a stop condition

chrismtb — Tue, 11 Jun 2019 11:45:31 GMT

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

Re: countif on historic data with a stop condition

chrismarlow — Tue, 11 Jun 2019 19:18:55 GMT

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;

topic Re: countif on historic data with a stop condition in QlikView

countif on historic data with a stop condition

Re: countif on historic data with a stop condition