topic Re: Line Chart based on workload (percentage) in QlikView

Line Chart based on workload (percentage)

Jonathan_Alm — Tue, 22 Jan 2013 11:05:14 GMT

Hi,

I am struggling with displaying the total workload for my staff at a given time.

I want to display in a linechart "the total workload for the beginning of each month: 2013-01, 2013-02, 2013-03 etc."

The following is the data that I am working with:

PersonID	ProjectID	Project	StartDate	EndDate	PercentageWorkLoad
ADAD9999	1	Windows	2011-01-04	2013-01-22	50
ADAD9999	2	Doors	2012-12-14	2013-04-22	50
JOAL1158	0	NotWorking	2012-08-23	2012-08-31	0
JOAL1158	3	Roof	2012-09-01	2012-10-30	100
DADA8822	0	NotWorking	2012-01-22	2012-12-31	0
NINI1234	4	Floor	2012-09-25	2013-05-01	50
NINI1234	1	Windows	2012-09-25	2013-05-01	50
NINI1234	0	NotWorking	2012-05-02	2013-06-21	0
OLOL1122	2	Doors	2012-09-25	2013-05-01	50
OLOL1122	5	Ceiling	2012-09-25	2013-06-01	50
OLOL1122	6	Tapestry	2011-01-01	2011-10-12	75
SVSV1212	4	Floor	2012-01-01	2013-01-01	50
SVSV1212	0	NotWorking	2013-01-02	2013-06-21	0
SVSV1212	0	NotWorking	2012-01-01	2013-01-01	0

I have created a table with all the months ranging from the first StartDate to the last EndDate:

MonthStart([StartDate] + IterNo()-1) as MonthStart,

Resident Employee while [StartDate] + IterNo()-1 <= [EndDate];

But now I am stuck in visualizing this in a line chart. I need to figure out how to get:

The sum of the percentage where the StartDate is less than the MonthStart and the EndDate is more than the MonthStart and dividie this with the distinct number of Employees.

This should then be expressed in my chart so that I can see that 2012-01 the total workload was X%, 2012-02 the total workload was Y% etc.

Kind regards, Jonathan

Re: Line Chart based on workload (percentage)

Tue, 22 Jan 2013 12:25:48 GMT

Hi,

please the attached file it may help you.

Niranjan M.

Re: Line Chart based on workload (percentage)

Jonathan_Alm — Tue, 22 Jan 2013 12:37:34 GMT

Hi,

what is missing from the example is that I need the total percentage for all employees at a given time. Therefore I need to divide the workload percentage with the number of employees that were active at that point.

So if I take 2012-10 as an example the sum of percentage is 150, but the average is 75 since two employees was within this range.

If I remove the PersonID from the chart, the sum of percentage will exceed 100%.

Kind regards, Jonathan

Re: Line Chart based on workload (percentage)

Jonathan_Alm — Wed, 23 Jan 2013 12:13:03 GMT

Hi Niranjan,

by taking the PercentageWorkLoad and dividing with distinct employee and removing the dimension employee from the chart I got the correct percentage.

Thanks for helping out

Kind regards, Jonathan