topic Re: Building a productivity Dashboard... in QlikView

Building a productivity Dashboard...

Wed, 16 Mar 2016 19:03:53 GMT

Hello, I'm trying to build a simple dashboard to monitor the Productivity of my team and I need some help...

I have two Excel tables:

1) WorkingHours contains the number of daily working hours of my employees

2) Production contains the number of items produced by each employee, each day.

To compute the hourly productivity, I need to sum the number of items produced by an employee (they work on different product types) and divide it by the number of hours worked in a given day.

I've already built a prototype in Excel and with a couple of pivot tables it is quite easy to achieve a result similar to this:

Employee Productivity (Items/hour)	15/12/2015	16/12/2015	17/12/2015	18/12/2015	Total
A	1,63	2,13	1,50	0,38	1,41
B	0,38	2,83	1,50	0,56	1,19
C	1,50	2,00	2,47	2,25	2,04

Now... how can I achieve the same result in QlikView?

This is the WorkingHours table:

Employee	Date	WorkingHours
A	15/12/2015	8
B	15/12/2015	8
C	15/12/2015	8
A	16/12/2015	8
B	16/12/2015	6
C	16/12/2015	7
A	17/12/2015	8
B	17/12/2015	8
C	17/12/2015	8,5
A	18/12/2015	8
B	18/12/2015	9
C	18/12/2015	4

And this is the Production table:

Employee	Date	Products	ItemsProduced
A	15/12/2015	X	1
A	15/12/2015	Y	8
A	15/12/2015	Z	2
A	16/12/2015	X	12
A	16/12/2015	Z	5
A	15/12/2015	Z	2
A	17/12/2015	X	4
A	17/12/2015	Y	5
A	17/12/2015	Z	3
A	18/12/2015	Z	3
B	15/12/2015	X	3
B	16/12/2015	X	12
B	16/12/2015	Z	5
B	17/12/2015	X	7
B	17/12/2015	Z	5
B	18/12/2015	Z	5
C	15/12/2015	X	2
C	15/12/2015	Y	3
C	15/12/2015	Z	4
C	16/12/2015	X	6
C	16/12/2015	Z	8
C	15/12/2015	Z	3
C	17/12/2015	X	5
C	17/12/2015	Y	7
C	17/12/2015	Z	9
C	18/12/2015	Z	9

Should I use a join when loading the tables in QlikView?

I'm not sure, since in the Production table I need to sum the number of products produced each day. Is there a way to build a total in the Load script?

Or would it be better to load both tables separately in QV and then aggregate them?

This is my first post in the QV Community... thank you in advance for any help!

Dario

Re: Building a productivity Dashboard...

swuehl — Wed, 16 Mar 2016 21:59:19 GMT

If you are not interested in the Products details, you can indeed aggregate the Production table in the script;

LOAD

Employee,

Date,

Sum(ItemsProduced) as TotalItemsProduced

FROM ProductionTable

GROUP BY Employee, Date;

JOIN

LOAD

Employee,

Date,

WorkingHours

FROM WorkingHoursTable;

Now you should have a single table with fields Employee, Date, TotalItemsProduced and WorkingHours.

Re: Building a productivity Dashboard...

amayuresh — Thu, 17 Mar 2016 05:18:44 GMT

Check this, is it answer your query

Script:

Hours:

LOAD * INLINE [

Employee, Date, WorkingHours

A, 15/12/2015, 8

B, 15/12/2015, 8

C, 15/12/2015, 8

A, 16/12/2015, 8

B, 16/12/2015, 6

C, 16/12/2015, 7

A, 17/12/2015, 8

B, 17/12/2015, 8

C, 17/12/2015, 8.5

A, 18/12/2015, 8

B, 18/12/2015, 9

C, 18/12/2015, 4

];

Production:

LOAD * INLINE [

Employee, Date, Product, Items

A, 15/12/2015, X, 1

A, 15/12/2015, Y, 8

A, 15/12/2015, Z, 2

A, 16/12/2015, X, 12

A, 16/12/2015, Z, 5

A, 15/12/2015, Z, 2

A, 17/12/2015, X, 4

A, 17/12/2015, Y, 5

A, 17/12/2015, Z, 3

A, 18/12/2015, Z, 3

B, 15/12/2015, X, 3

B, 16/12/2015, X, 12

B, 16/12/2015, Z, 5

B, 17/12/2015, X, 7

B, 17/12/2015, Z, 5

B, 18/12/2015, Z, 5

C, 15/12/2015, X, 2

C, 15/12/2015, Y, 3

C, 15/12/2015, Z, 4

C, 16/12/2015, X, 6

C, 16/12/2015, Z, 8

C, 15/12/2015, Z, 3

C, 17/12/2015, X, 5

C, 17/12/2015, Y, 7

C, 17/12/2015, Z, 9

C, 18/12/2015, Z, 9

];

t1:

Load

Sum(WorkingHours) as TotalHours, Employee&Date as Key

Resident Hours

Group by Employee&Date;

join

Load Employee, Date, Product, Items, Employee&Date as Key

Resident Production;

Drop table Hours,Production;

Output:

Used Dimension: Date, Employee

Expression:

Hours=Avg(TotalHours)

Qty=sum(Items)

Productivity=sum(Items)/Avg(TotalHours)

Re: Building a productivity Dashboard...

Thu, 17 Mar 2016 11:08:09 GMT

Hello all,

Thank you all for the useful feedback.

I've built a simple prototype (see attached) that seems to work.

Now I need to enhance it and add a new KPI that shows the average productivity of the team. In this way I should be able to build a comparison chart that shows the productivity of employee A (or B, or C...) against the team average.

I've spent some time playing with the Pivot objects in the Qlikview document, but I've not been able to add a column that computes the team average productivity for each day.... any suggestion?

Re: Building a productivity Dashboard...

swuehl — Thu, 17 Mar 2016 15:43:45 GMT

Maybe just like attached?

Changing your expression to

=sum(ItemsProduced)/Sum(WorkingHours)

and enabling partial sums on presentation tab.

Re: Building a productivity Dashboard...

Thu, 17 Mar 2016 18:59:47 GMT

Whoa! It was *that* easy?

I had already tried to enable the partial sums, but wih the old expression =sum(ItemsProduced)/WorkingHours there was no effect, the Totals column did not show in the table. Thanks for the tip.

Now, the next step... I need to build a histogram chart that compares the daily Productivity of a given employee with the team average.

Ideally, the result should be similar to the attached screenshot.

I've built the bar chart easily, now I'm struggling to add a line that shows the team average... I'm sure there's a way to do it, but how?

I've been experimenting with additional expressions in the bar chart, but so far without result.

Re: Building a productivity Dashboard...

swuehl — Fri, 18 Mar 2016 10:20:05 GMT

You can create a second expression using the TOTAL qualifier like

=sum(Total<Date>ItemsProduced)/Sum(Total<Date>WorkingHours)

and set display options to line for this expression.

Re: Building a productivity Dashboard...

Fri, 18 Mar 2016 17:45:12 GMT

Thanks Swuehl, I've tried your suggestion.

Unfortunately the expression

=sum(Total<Date>ItemsProduced)/Sum(Total<Date>WorkingHours)

that should compute the team productivity is influenced by the selection: if I select a given employee, the result changes and the line shows the productivity of the selected employee instead of the whole team.

I think that instead of using the Total<Date> modifier, I should use a different modifier that computes the overall team productivity of a given day, unrespective to the selected employee.

Re: Building a productivity Dashboard...

swuehl — Fri, 18 Mar 2016 18:28:46 GMT

You can combine the proposed solution with a set expression like

=sum({1} Total<Date>ItemsProduced)/Sum({1} Total<Date>WorkingHours)

This may force the chart to show all Employees all the time, but you can circumvent that with multiplying by something that returns zero when out of selection, like Count(DISTINCT Employee).

See attached.

Re: Building a productivity Dashboard...

Fri, 18 Mar 2016 21:19:15 GMT

Thank you Swuehl, your suggestion works well.

I've already implemented it in my real dashbord and the behaviour is exaclty what I was expecting.