Advanced Charting

[Kris1]

Hi All, 

Thanks in advance for your reply,

i am having data with 4 fields, Program, Stage, Date, Status

Program Name	Stage	Date	Status
Program1	Stage1
Program1	Stage2	11/1/2018	Completed
Program1	Stage3	15/5/2019	Completed
Program1	Stage4	21/2/2021	Completed
Program1	Stage5	1/1/2022
Program2	Stage1	14/12/2017	Completed
Program2	Stage2	12/10/2018	Completed
Program2	Stage3	20/12/2019	Completed
Program2	Stage4
Program2	Stage5	22/2/2022
Program3	Stage1
Program3	Stage2
Program3	Stage3	23/12/2019	Completed
Program3	Stage4	20/9/2020	Completed
Program3	Stage5	21/12/2021

i want one straight in qliksense with 4 fields Program, Overall Status, Next Stage, Next Stage Date

Program Name	Overall Status	Next Stage	Next Satage Date
Program1	Stage4	Stage5	1/1/2022
Program2	Stage3	Stage4	-
Program3	Stage4	Stage5	21/12/2021

Overall Status - In particular program what is the latest completed stage

Next Stage - Stage after latest completed stage

Next Stage Date - Date of stage after latest completed stage

[Taoufiq_Zarra]

@Kris1  ,

can we propose something in load script ?

you have always 5 stages ?

for exemple for the first one you can use :

=FirstSortedValue({<Status={'Completed'}>}Stage,-Date)

output:

[Kris1]

Hi Taoufiq ZARRA,

Thank You for your valuable Solution. This is just dummy data, I am having 12 stages in my project. No problem any where backend or front end works. 

Another thing Taoufiq, in one program  

Program Name	Stage	Date	Status
Program4	Stage1
Program4	Stage2	11/1/2018	Completed
Program4	Stage3	25/5/2020	Completed
Program4	Stage4	25/5/2020	Completed
Program4	Stage5	1/1/2022

in program4 two stages having same dates (within a day two programs are completed). In this case also i need to show overall Status-Stage4. But your solution we are getting null values.

[Taoufiq_Zarra]

@Kris1  in script for example you can use :

Data:

LOAD * INLINE [
    Program Name , Stage , Date , Status
    Program1 , Stage1 ,   ,  
    Program1 , Stage2 , 11/1/2018 , Completed
    Program1 , Stage3 , 15/5/2019 , Completed
    Program1 , Stage4 , 21/2/2021 , Completed
    Program1 , Stage5 , 1/1/2022 ,  
    Program2 , Stage1 , 14/12/2017 , Completed
    Program2 , Stage2 , 12/10/2018 , Completed
    Program2 , Stage3 , 20/12/2019 , Completed
    Program2 , Stage4 ,   ,  
    Program2 , Stage5 , 22/2/2022 ,  
    Program3 , Stage1 ,   ,  
    Program3 , Stage2 ,   ,  
    Program3 , Stage3 , 23/12/2019 , Completed
    Program3 , Stage4 , 20/9/2020 , Completed
    Program3 , Stage5 , 21/12/2021 ,  
    Program4 , Stage1 ,   ,  
    Program4 , Stage2 , 11/1/2018 , Completed
    Program4 , Stage3 , 25/5/2020 , Completed
    Program4 , Stage4 , 25/5/2020 , Completed
    Program4 , Stage5 , 1/1/2022 ,  
];



left join load [Program Name],MaxString(Stage) as Stage,Date(max(Date)) as Date,'1' as Flag resident Data where Status='Completed' group by [Program Name] ;

Tmp:
noconcatenate

load * resident Data order by [Program Name],Stage;

drop table Data;


Final:
noconcatenate
load [Program Name] , Stage , Date , Status,if(peek([Program Name])=[Program Name] and Flag<>1 and peek(Flag)=1,2,Flag) as Flag resident Tmp;

drop table Tmp;

dimension Program Name

measure :

=Only({<Flag={1}>}Stage) for Overall Status

=Only({<Flag={2}>}Stage)->next stage

=Only({<Flag={2}>}Date)-> next stage date

output: