Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Building a productivity Dashboard...

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/201516/12/201517/12/201518/12/2015Total
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:

   

EmployeeDateWorkingHours
A15/12/20158
B15/12/20158
C15/12/20158
A16/12/20158
B16/12/20156
C16/12/20157
A17/12/20158
B17/12/20158
C17/12/20158,5
A18/12/20158
B18/12/20159
C18/12/20154

And this is the Production table:

   

EmployeeDateProductsItemsProduced
A15/12/2015X1
A15/12/2015Y8
A15/12/2015Z2
A16/12/2015X12
A16/12/2015Z5
A15/12/2015Z2
A17/12/2015X4
A17/12/2015Y5
A17/12/2015Z3
A18/12/2015Z3
B15/12/2015X3
B16/12/2015X12
B16/12/2015Z5
B17/12/2015X7
B17/12/2015Z5
B18/12/2015Z5
C15/12/2015X2
C15/12/2015Y3
C15/12/2015Z4
C16/12/2015X6
C16/12/2015Z8
C15/12/2015Z3
C17/12/2015X5
C17/12/2015Y7
C17/12/2015Z9
C18/12/2015Z9

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

1 Solution

Accepted Solutions
MVP
MVP

Maybe just like attached?

Changing your expression to

=sum(ItemsProduced)/Sum(WorkingHours)

and enabling partial sums on presentation tab.

View solution in original post

9 Replies
MVP
MVP

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.

Creator III
Creator III

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:

1.PNG

Used Dimension: Date, Employee

Expression:

Hours=Avg(TotalHours)

Qty=sum(Items)

Productivity=sum(Items)/Avg(TotalHours)

Not applicable

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?

MVP
MVP

Maybe just like attached?

Changing your expression to

=sum(ItemsProduced)/Sum(WorkingHours)

and enabling partial sums on presentation tab.

View solution in original post

Not applicable

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.Mockup.png

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.

MVP
MVP

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.

Not applicable

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.

MVP
MVP

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.

Not applicable

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.