topic Re: Advanced Charting in App Development

Advanced Charting

Kris1 — Fri, 15 Nov 2024 23:30:16 GMT

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

Re: Advanced Charting

Taoufiq_Zarra — Fri, 19 Nov 2021 10:11:42 GMT

@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:

Re: Advanced Charting

Kris1 — Fri, 19 Nov 2021 10:51:13 GMT

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.

Re: Advanced Charting

Taoufiq_Zarra — Fri, 19 Nov 2021 11:35:36 GMT

@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: