topic Re: Gantt Chart using a pivot Table in QlikView

Gantt Chart using a pivot Table

Wed, 04 Jan 2017 11:01:35 GMT

I am working with data that has a start date and end date. I have researched building a Gantt chart using a pivot table and I am running into some road blocks. I am new to development, so I may be a little behind the curve. I have been able to create a stand alone Gantt chart, but need to add columns in front and the only way I have seen that this is possible is to use a pivot table.

I have seen a discussion in this forum, but the one I was looking at, was more for someone with a lot more knowledge than I have. I have my pivot built out.

I also have the Gantt calculations as follows:

1: MAX([AP End Date])-MIN([Load Date])

2: interval(Max ([AP End Date] )-Min ([AP Start Date]),'D') & ' days'

In my pivot table I have the following fields:

Primary Owner

LOB 1

LOB 2

AP Start Date

AP End Date

AP ID

AP Name

ID Source

Severity

AP Health

Any help would be greatly appreciated. Thank you for your time!

Re: Gantt Chart using a pivot Table

petter — Wed, 04 Jan 2017 11:17:08 GMT

Do you have some screenshots and or a sample QVW so it is quicker to help you out?

Re: Gantt Chart using a pivot Table

Wed, 04 Jan 2017 12:03:51 GMT

The top one is the pivot table, I had to change some things, which is why it is a bit odd looking, I had to hide items, due to this being for work and I cannot show the actual data.

The bottom is the Gantt Chart that I would like to add to the pivot.

Re: Gantt Chart using a pivot Table

petter — Wed, 04 Jan 2017 17:20:12 GMT

Is it something like this you had in mind?

Re: Gantt Chart using a pivot Table

petter — Wed, 04 Jan 2017 18:19:45 GMT

A more realistic example:

You have to do some data-shaping in the load script to generate a helper table (GANTT_CELLS) for the individual days to be able to use them as a dimension called Status that is pivoted out horisontally and using the value of 1 to indicate that this day is within the start and end date....

Re: Gantt Chart using a pivot Table

Wed, 04 Jan 2017 21:21:03 GMT

Yes, the second chart is exactly what I am looking to do. What do you mean by "Data Shaping" and creating a helper table? Can you show an example, please?

Thank you so much for your time!

Re: Gantt Chart using a pivot Table

Wed, 04 Jan 2017 21:22:09 GMT

but instead of Days across the top, I would need a date like what I have in the screen show, or Month.

Re: Gantt Chart using a pivot Table

petter — Wed, 04 Jan 2017 22:22:48 GMT

I already attached a QVW that is the example. It has a load script that does the data shaping.

The example is based on a table that looks very much like the pivot tables first 9 columns and I guess that is the same as your table is already right?

However the day# and the flag that indicates that the day participates in an active range of days for the task is generated with the LOAD .... RESIDENT .... WHILE part giving you a helper table that is associated with the first table via some unique identifier for each tracked task. This is what I call data shaping.

Here is the load script that you find in the attached QVW-file too:

When it comes to using data instead of day# you can just replace the last dimension for the Pivot Table with the Date field. The Date field has already been generated in the helper table. Some formatting is necessary to leave out the year part since it takes too much space.

Re: Gantt Chart using a pivot Table

Wed, 04 Jan 2017 22:25:07 GMT

Thank you so much! I did not see an attachment, I apologize.

Re: Gantt Chart using a pivot Table

petter — Thu, 05 Jan 2017 00:20:24 GMT

Here is how an attachment looks like (you find the real attachment further up):

Re: Gantt Chart using a pivot Table

petter — Thu, 05 Jan 2017 00:21:07 GMT

If you are happy with the suggestions please mark the question as answered

Re: Gantt Chart using a pivot Table

Thu, 05 Jan 2017 00:25:25 GMT

The first attachment never came up on my end. Thank you for all of your help.