How to verify sequential steps are chronologically ordered

[Maite_CA]

Hello, 

I have an excel file with activities decomposed in sequential Phases and the Planned End Dates for each one of those phases. I am trying now to implement some KPIs in Qlik Sense that allows me tracking the activities easier, with a Master Schedule view using a Pivot Table. 

My data is something like this: 

Activity Key	Activity Name	Phase	Status	Planned END dates
ACT_0001	ACT_0001: Electronic	P0	Delivered	21/06/2019
ACT_0001	ACT_0001: Electronic	P1	Delivered	20/12/2019
ACT_0001	ACT_0001: Electronic	P2	Delivered	16/08/2019
ACT_0001	ACT_0001: Electronic	P3	Delivered	20/12/2019
ACT_0002	ACT_0002: Electrical	P0	Delivered	31/12/2018
ACT_0002	ACT_0002: Electrical	P1	Delivered	01/02/2019
ACT_0002	ACT_0002: Electrical	P2	Delivered	01/03/2019
ACT_0002	ACT_0002: Electrical	P3	Late	18/12/2020
ACT_0002	ACT_0002: Electrical	P4	Late	18/12/2020
ACT_0002	ACT_0002: Electrical	P5	Late	18/12/2020
ACT_0002	ACT_0002: Electrical	P6	Delivered	27/11/2020
ACT_0003	ACT_0003: Structure	P0	Delivered	04/10/2019
ACT_0003	ACT_0003: Structure	P1	Delivered	12/02/2020
ACT_0003	ACT_0003: Structure	P2	Delivered	04/10/2019
ACT_0003	ACT_0003: Structure	P3	Delivered	06/11/2020
ACT_0004	ACT_0004: Systems	P0	Delivered	19/04/2019
ACT_0004	ACT_0004: Systems	P1	Late	18/12/2020
ACT_0004	ACT_0004: Systems	P2	In progress	29/01/2021
ACT_0004	ACT_0004: Systems	P3	In progress	29/01/2021
ACT_0004	ACT_0004: Systems	P4	In progress	29/01/2021
ACT_0004	ACT_0004: Systems	P5	Late	15/12/2019

As you can see in the table, one of the problems I find is that people can write the date that they want, and sometimes they forget to actualize the date for the next Phases when they modify one of them. That creates incongruencies in the data as they can't deliver a phase if they don't deliver first the precedent one. 

I have tried to create a new dimension to verify that the Planned End dates are chronologically order for the different phases of an activity but I haven't found the way. Could you help me please? 

Thank you in advance,

Maite_CA

[ArnadoSandoval]

Hi @Maite_CA 

I wrote the attached application (z_Verify_Seq.qvf) that verifies the sequence of the sample data you provided, I think it will help you to identify those records out of sequence, the screenshot below shows the offending records in your scheduled highlighted in yellow

You may notice two new fields, Seq and Phase_Seq,  the script creates them implementing the Autonumber - script function ; The first field Seq is the Autonumber of the [Planned END dates] field on the [Activity Key] field, after ordering (sorting) the data by [Activity Key] + [Planned END dates];  for the second field Phase_Seq, I orders the data by [Activity Key] + Phase, then I applied Autonumber of the Phase fields on the 'P-' & [Activity Key] field; with the second Autonumber I used the trick of adding a text prefix (P-) to the [Activity Key] so Qlik creates a different sequence numbers, this way Seq and Phase_Seq are 1 base sequences driven by the [Activity Key].

Perhaps the script could be improved because it creates 4 temporary tables for the activities, due to the required sorts and I did not want to mix the Autonumber with the Order By, although it may be possible to have them together.

Please, let us know if this approach resolve your problem.