I have a Jobs table which contains various tasks, their current status, dates of execution, etc laid out as below (this is a massively simplified version of what I have).
Job_No Task Status Start_Date End_Date Current_Rec Date ABC123 Planning Started 05-Nov-18 N 05-Nov-18 ABC123 Planning Comp 05-Nov-18 10-Nov-18 Y 10-Nov-18 ABC123 Survey Not Started N 05-Nov-18 ABC123 Survey Started 10-Nov-18 N 10-Nov-18 ABC123 Survey Comp 10-Nov-18 15-Nov-18 Y 15-Nov-18 ABC123 Design Not Started 05-Nov-18 N 05-Nov-18 ABC123 Design Started 15-Nov-18 Y 15-Nov-18 ABC123 Build Not Started Y 05-Nov-18 ABC123 Audit Not Started Y 05-Nov-18
I need to be able to write measures which allow me to show jobs based on the dates and status' of several tasks but can't work out how to do so. In SQL I would create subqueries and use those as criteria but I'm not aware of a way to do this in Qlik without making a ridiculously complex table during data load.
Examples of what I'm trying to do is count all jobs where Planning and Design tasks are Complete but Build has not started. I'd want to dimension those on either the latest date of completion or on the dwell since the latest completion. I'm guessing I need to do something with Aggr but I've really got no firm ideas.
