9 Replies Latest reply: Mar 18, 2016 5:19 PM by Dario Orbecchi RSS

    Building a productivity Dashboard...

    Dario Orbecchi

      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

        • Re: Building a productivity Dashboard...
          Stefan Wühl

          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...
            Mayuresh Athalye

            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)

            • Re: Building a productivity Dashboard...
              Dario Orbecchi

              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?