Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
chrismtb
Creator
Creator

countif on historic data with a stop condition

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

 

1 Reply
chrismarlow
Specialist II
Specialist II

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;

20190611_1.png

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;