
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/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
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe just like attached?
Changing your expression to
=sum(ItemsProduced)/Sum(WorkingHours)
and enabling partial sums on presentation tab.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe just like attached?
Changing your expression to
=sum(ItemsProduced)/Sum(WorkingHours)
and enabling partial sums on presentation tab.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
