Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
xlitzdrama
Creator
Creator

Creating a Gantt Chart

I have 7 Different dates related to one ID,

I would like to show lifecycle of this ID From Start of Task 1 to End of Task 7

Application Received On -> Check Date 1 -> Check Date 2 -> Check Date 3 -> Check Date 4 -> Check Date 5 -> Check Date 6

All these dates are in dimension.

Labels (4)
6 Replies
anthonyj
Creator III
Creator III

Hi,

@Patric_Nordstrom  has put together some fantastic visualization apps demonstrating how to do various charts in Qlik. In this post he supplied the link for downloading the QVF that explains how to create a Gantt chart.

https://community.qlik.com/t5/Qlik-Sense-Documents/Top-10-Viz-tips-Qonnections-2019-Part-I/ta-p/1580...

There are 5 or 6 of these apps he's created showcasing visualisations. They're all worth downloading for reference.

 

Regards

Anthony

xlitzdrama
Creator
Creator
Author

Yes, I have previously tried this in this task are individually defined and in my case one id have different task below it, all ID having different Start Date and End Date under their Tasks, 

Example:

Load * Inline [

ID,Application_Received_On,Check1,Check2,Check3,Check4,Check5,Check6

1,01/01/2021,02/01/2021,02/01/2021,03/01/2021,04/01/2021,05/01/2021,06/01/2021

2,02/01/2021,03/01/2021,02/01/2021,05/01/2021,07/01/2021,09/01/2021,16/01/2021

];

 

Now as you can see in Y-Axis there should be one ID and different dates should be shared in Grouped Format and not in Stacked Form.

xlitzdrama_0-1649143522220.png

 

anthonyj
Creator III
Creator III

Hi @xlitzdrama,

Thank you for clarifying your requirements. So you have different stages to the one ID which you want to have shown in a Gantt chart. From your data it looks like there is one date for each of these sections and a Gantt chart requires a start and end date to calculate duration. For a single date an option I've used before is the distribution plot with a similar technique as the Gantt chart.

anthonyj_0-1649219159406.png

 

I have pivoted your data so that the dates are in one column along with the names of the sections.

Data2:
CrossTable(Status, Dates, 1)
load *
resident Data;

What I have called the Status is the point and the Y-axis is a combination of ID & Status. The measure is the date field.

You are still able to use the Gantt chart technique as demonstrated by Patric's app but you would need to add an End Date and a Duration.

Data2:
CrossTable(Status, Dates, 1)
load *
resident Data;

Data3:
Load
*,
EndDate - StartDate as Duration;

Load
ID,
Status,
Dates as StartDate,
if(ID <> previous(ID),
Dates, previous(Dates) ) as EndDate
Resident Data2
order by ID, Status desc, Dates ;

drop table Data2;

This is assuming the end date is the start of the next section.

anthonyj_1-1649219564873.png

So, ID & Status is the Y axis. Create a master measure for the min(StartDate) and set the color to white. Add the duration as the second measure. Set the X-axis as min(StartDate) and Max(EndDate). In the Add-ons enter in the function today() as a reference line.

I hope this makes sense, but I think the first step is to get your data into a shape that Qlik can more efficiently deal with.

Thanks

Anthony

xlitzdrama
Creator
Creator
Author

Hi, Thanks for your answer I followed it but could not get to know how Status is coming into your data in mine Y Axis only 1- and 2- is coming.

xlitzdrama_0-1649761656327.png

Data:
Load * Inline [

ConsignmentID,Application_Received_On,Check1,Check2,Check3,Check4,Check5,Check6

1,01/01/2021,02/01/2021,02/01/2021,03/01/2021,04/01/2021,05/01/2021,06/01/2021

2,02/01/2021,03/01/2021,02/01/2021,05/01/2021,07/01/2021,09/01/2021,16/01/2021

];

Data2:
CrossTable(Status, Dates, 1)
load *
resident Data;

Data3:
Load
*,
EndDate - StartDate as Duration;

Load
ConsignmentID,
Status,
Dates as StartDate,
if(ConsignmentID <> previous(ConsignmentID),
Dates, previous(Dates) ) as EndDate
Resident Data2
order by ConsignmentID, Status desc, Dates ;

drop table Data2;

xlitzdrama
Creator
Creator
Author

Dates:
Load
ConsignmentID,
Date1 as Dates
FROM [lib://Extraction/MODEL_Final.qvd]
(qvd) ;
Concatenate
Load
ConsignmentID,
Date2 as Dates
FROM [lib://Extraction/MODEL_Final.qvd]
(qvd) ;
Concatenate
Load
ConsignmentID,
Date3 as Dates
FROM [lib://Extraction/MODEL_Final.qvd]
(qvd) ;
Concatenate
Load
ConsignmentID,
Date4 as Dates
FROM [lib://Extraction/MODEL_Final.qvd]
(qvd) ;
Concatenate
Load
ConsignmentID,
Date5 as Dates
FROM [lib://Extraction/MODEL_Final.qvd]
(qvd) ;
Concatenate
Load
ConsignmentID,
Date6 as Dates
FROM [lib://Extraction/MODEL_Final.qvd]
(qvd) ;
Concatenate
Load
ConsignmentID,
Date7 as Dates
FROM [lib://Extraction/MODEL_Final.qvd]
(qvd);

Store Dates into [lib://Extraction/Dates.qvd]
(qvd);

Drop Table Dates;

Dates_Status:
Load
Distinct ConsignmentID From [lib://Extraction/MODEL_Final.qvd]
(qvd);
Left Join
LOAD
Distinct ConsignmentID,
"Application Pending Status"
FROM [lib://Extraction/Pending_At_Stage.qvd]
(qvd);


Data2:
CrossTable(ConsignmentID,Dates,1)
Load * Resident Dates_Status;

Drop Table Dates_Status;

Data3:
Load *, EndDate-StartDate as Duration;

Load
ConsignmentID,
"Application Pending Status",
Dates as StartDate,
if(ConsignmentID<>Previous(ConsignmentID),
Dates, previous(Dates) ) as EndDate
Resident Data2
order by ConsignmentID, Dates,"Application Pending Status" desc ;

drop table Data2;

xlitzdrama_0-1649762935189.png

I am attaching the resultant Data3 Table by ConsignmentID to my main model, There are 7 Different Status Present Under Application Pending Status which is now coming under StartDate and EndDate and Under Application Pending Status , Application Pending Status written is coming as in Screenshot attached. Duration field is empty.

 

anthonyj
Creator III
Creator III

Hi,

By the way you're building your table it looks like your source data has one line per ConsignmentID with the 7 dates as headers. Each of these date columns should have the reason for this date. ie Date1 should be renamed to "Application Received Date". Date2 should be "Check1" and so on... These new column headers will become the Status when you perform the crosstable.

Dates:
Load
ConsignmentID,
Date1 as [Application Received Date],

Date2 as [Check 1],

etc....
FROM [lib://Extraction/MODEL_Final.qvd]
(qvd) ;

Data2:
CrossTable(Status, Dates, 1)
load *
resident Dates;

drop table Dates;

Data3:
Load
*,
EndDate - StartDate as Duration;

Load
ID,
Status,
Dates as StartDate,
if(ConsignmentID <> previous(ConsignmentID),
Dates, previous(Dates) ) as EndDate
Resident Data2
order by ConsignmentID, Status desc, Dates ;

Now you will have a table where all the dates are in one column and you will have column that outlines the Status that is relevant to that date.

You have a table that is your Pending Status that is loading in ConsignmentID's and Status however there's no link to the date that is relevant to that status. Your joins are only by ConsignmentID which won't match dates with the status.

If your dates relate to status then rename these columns to the status and do a crosstable load. This will create a table with 7 ConsignmentID's, each with its own status and date.

I hope this goes some way to clearing up my initial response.

Regards

Anthony