17 Replies Latest reply: Jun 7, 2017 12:22 PM by George Beaton RSS

    Actual Hours Worked versus Hours Available

    Casey McDonald

      I'm trying to create expressions that will show "% Hours entered yesterday" among other visualizations and calculations.

       

      I have a table OPS_CF which lists every mechanic and the hours they can work in a day (in this case most are 8). This field is STD_HOURS_AVAILABLE and represents the total hours of standard time a mechanic can work on any given day.

       

      I have a second table OPS_WRCF which lists mechanics and the hours they worked on any given work order (i.e. four mechanics can work on one work order over four days, so one mechanic worked 8 hours a day on 6/1-6/3 he will have 3 records for a work order.)

       

      Showing "% Hours entered Yesterday" would be calculating the actual hours put in from OPS_WRCF for yesterday and showing what percentage that is in comparison to the total hours available from the OPS_CF table for that day and that mechanic.

       

      Is this possible? Would it be better to create a table with all the data in it? How would I write this in an expression, or would it be better to script?

       

      Further explanation: John Smith is available to work 8 hours a day. He works on Work Order #1 for three days, and works 8 hours on Day 1, 6 hours on Day 2, and 8 hours on Day 3. His total actual hours worked is 22, but he was available for 24. I am trying to show all actual hours a mechanic put in for a specific day in comparison to what hours all of those mechanics were actually available on a specific day.

       

      Thanks!

        • Re: Actual Hours Worked versus Hours Available
          Hannah Johnson

          Hello Casey,

           

          You are asking a lot of questions . Do you have an example qvw you can share?

           

          ,Hannah

            • Re: Actual Hours Worked versus Hours Available
              Casey McDonald

              Unfortunately, I haven't built anything yet (as I am trying to get input into how to go about building what I need), and don't use Qlik View, so there is no .qvw.

                • Re: Actual Hours Worked versus Hours Available
                  Prashanth Reddy

                  Hi Casey,

                   

                  I built same kinda app and may be it varies a little bit. I would like to share my procedure.

                   

                  Background: I have around 20 resources and i need to calculate their utilization by excluding Public Holidays, Sat and Sunday and their PTOs.

                   

                  We have a sheet/table where we are capturing the actual hours they worked. Basing on the start date of resource/worker and current date, we are calculating available hours (for us 8 Hrs per day).

                   

                  Also, we are capturing Public holidays in separate table and Saturday and Sunday will be taken care by the function "NetWorkDays"

                   

                  I'm not sure about your data model but in general, I'm providing you the expression which gives you available hours per each resource/worker.

                   

                  Sum(TOTAL <[Date Entered], Resource, Department, Month, Quarter, Year> 

                  Aggr(NetWorkDays (Date#(Min(date([Date Entered],'M/D/YYYY'))),Date#(max(date([Date Entered],'M/D/YYYY')))),

                  Resource, Department, Month, Quarter, Year)*8) -

                  sum(TOTAL <[Date Entered], Resource, Department, Month, Quarter, Year>aggr(sum(if(IsHoliday = 'Y', '1', 0))*8,Resource, Department, Month, Quarter, Year))

                   

                  Note:

                  [Date Entered] - Resource/worker worked date.

              • Re: Actual Hours Worked versus Hours Available
                George Beaton

                Hi Casey,

                I would always recommend doing as much pre processing in the script as is possible.  In your case, I would suggest creating an extra column in the OPS_WRCF table which gave you the amount of time they worked on the work order expressed as a percentage of their total time available.  I am presuming there is a row per mechanic per day for this.

                 

                To calculate that value, you will need the hours available for each mechanic from the OPS_CF table.  There are a couple of functions in QlikSense that will be able to help you, Mapping Load and Applymap.

                 

                You could do something like this:

                 

                Map_MechToHours:
                Mapping Load 
                 MechanicID
                ,HoursAvailable
                From OPS_CF;
                
                Load *
                , ApplyMap('Map_MechToHours', MechanicID)/hoursWorkedOnWorkOrder as PercentTimeWorked
                Resident OPS_WRCF;
                

                 

                You can then use your new metric PercentTimeWorked much easier in the front end.

                Hope this gets you closer to the answer.

                George

                  • Re: Actual Hours Worked versus Hours Available
                    Casey McDonald

                    The issue is there can be numerous rows per mechanic, per day, if a mechanic worked on three separate work requests on one day. So each records would have to be a percent of their total actual hours worked that day and then divided by whatever hours they were available to work (could be 8, could be 10 as it isn't standard for all mechanics).

                     

                    Would that script still work?

                      • Re: Actual Hours Worked versus Hours Available
                        George Beaton

                        Hi Casey,

                        Great.  Yes that code would still work.  If a mech worked for say 2 out of 8 hours on one job and 6 out of 8 on another, if you were to sum (in the front end) the total percentage for the mechanic for that day, it would give you 1 (100%).  You'll have to adapt it for your specific use case, but the principle will work.

                        Good luck

                        George

                          • Re: Actual Hours Worked versus Hours Available
                            Casey McDonald

                            I think we are getting there. A final thought:


                            Now, if I'm dividing the actual hours worked for a mechanic for a day, but summing the hours and dividing by STD_HOURS_AVAIL, will QLIK apply that field to each record?

                             

                             

                            Example: OPS_WRCF

                            Date    Mechanic    WR_ID    HoursRecorded

                            6/2/17    Mech1    WR_12          2.5

                            6/2/17    Mech1    WR_11          2.5

                            6/2/17    Mech1    WR_09          3

                             

                             

                            Total hours recorded for 6/2/17 equals 8, so in this case "sum(HoursRecorded)" works fine.

                             

                            Now I want to divide that by that mechanic's STD_HOURS_AVAIL, found in the OPS_CF table. In this case, Mech1 works 10 hour days, and has only one record in OPS_CF (as this table is a list of all mechanics).

                             

                            Once I associate the WRCF table and CF table I can't sum(STD_HOURS_AVAIL) because it will attribute that field value for that mechanic for each records (I'm assuming) so it looks like this:

                             

                            Example: OPS_WRCF

                            Date    Mechanic    WR_ID    HoursRecorded STD_HOURS_AVAIL

                            6/2/17    Mech1    WR_12          2.5                              10

                            6/2/17    Mech1    WR_11          2.5                              10

                            6/2/17    Mech1    WR_09          3                                10

                             

                             

                            So it will sum HoursRecorded to equal 8, but sum STD_HOURS_AVAIL to equal 30 hours available for one day, which isn't correct.

                             

                            Will this script prevent that and only attribute the STD_HOURS_AVAIL to the mechanic once per day, despite however many records that mechanic has for a given day?

                              • Re: Actual Hours Worked versus Hours Available
                                George Beaton

                                Hi Casey,

                                The script will still work and not double count.  The OPS_WCRF table should not contain the STD_HOURS_AVAIL column.  Rather, it should only contain the PercentTimeWorked  field.  So, OPS_WRCF will look like this:

                                 

                                DateMechanicWRD_IDHoursRecordedPercentTimeWorked
                                6/2/17  Mech1    WR_12

                                2.5

                                0.25
                                6/2/17  Mech1    WR_112.50.25
                                6/2/17  Mech1    WR_0930.3

                                 

                                Your OPS_CF table will continue to look like this:

                                 

                                MechanicSTD_HOURS_AVAIL
                                Mech110
                                Mech28

                                 

                                When you reload the application, the QIX engine associates the two tables on the shared field Mechanic.  The key point here is that it associates, rather than joins.  So when you do SUM(STD_HOURS_AVAIL) (and presuming you have selected Mech1 somewhere), the result will be 10, and not 30.

                                • Re: Actual Hours Worked versus Hours Available
                                  George Beaton

                                  No, it's because I wrote psuedo code

                                   

                                  Map_MechToHours: 
                                  Mapping Load 
                                  MechanicID 
                                  ,HoursAvailable 
                                  From OPS_CF; 
                                  

                                   

                                  The From is wrong.  This would have to refer to your actual data.  I'm not sure how you're connecting, but it would need to be something like

                                  Map_MechToHours:  
                                  Mapping Load   
                                   MechanicID  
                                  ,HoursAvailable  
                                  Resident OPS_CF;  
                                  

                                   

                                  or

                                   

                                  Map_MechToHours:  
                                  Mapping Load   
                                   MechanicID  
                                  ,HoursAvailable  
                                  From [c:\qvds\OPS_CF.qvd](qvd);  
                                  

                                   

                                  Does that help?

                                    • Re: Actual Hours Worked versus Hours Available
                                      Casey McDonald

                                      Ah okay. I made the necessary changed but it just created a new table called Map_MechToHours with everything from WRCF in it, plus PercentHoursWorked. It also generated a massive synethic key. Did I do something wrong?

                                        • Re: Actual Hours Worked versus Hours Available
                                          George Beaton

                                          Yes, it sounds like it.  Mapping tables are automatically dropped before Sense does the associations.  It sounds like it stayed in memory for some reason.  Did you definitely use the Mapping keyword?

                                            • Re: Actual Hours Worked versus Hours Available
                                              Casey McDonald

                                              That I did, I modified the script to include the longer table names and it reads as:

                                               

                                              Map_CFtoHoursAvailable: 

                                                Mapping Load  

                                                CF_ID, 

                                                V_QLIK_OPS_CF.STD_HOURS_AVAIL

                                                Resident V_QLIK_OPS_CF; 

                                               

                                              Load * 

                                                ,[HOURS_STRAIGHT]/ApplyMap('Map_CFtoHoursAvailable', CF_ID) as PercentTimeRecorded 

                                              Resident V_QLIK_OPS_WRCF; 

                                               

                                              Is it a preceding load issue? I just created a new section of script and called it "Mapping" and put it as the last section in the data load editor.

                                                • Re: Actual Hours Worked versus Hours Available
                                                  George Beaton

                                                  I think the problem is with your second load there.  Sense is creating an identical table to V_QLIK_OPS_WRCF with the addition of the PercentTimeLoaded column then joining on every matching column.


                                                  When you load in V_QLIK_OPS_WRCF the first time, call it something like T1.  Then, modify your code like this:

                                                  Map_CFtoHoursAvailable: 
                                                    Mapping Load  
                                                    CF_ID, 
                                                    V_QLIK_OPS_CF.STD_HOURS_AVAIL
                                                    Resident V_QLIK_OPS_CF; 
                                                  
                                                  V_QLIK_OPS_WRCF:
                                                  Load * 
                                                    ,[HOURS_STRAIGHT]/ApplyMap('Map_CFtoHoursAvailable', CF_ID) as PercentTimeRecorded 
                                                  Resident T1; 
                                                  
                                                  Drop table T1;
                                                  

                                                   

                                                  That should do it.

                                                    • Re: Actual Hours Worked versus Hours Available
                                                      Casey McDonald

                                                      I'll give it a shot. I do have final question: How would this interpret negative hours? Sometimes a mechanic entered in 4 hours and meant to input 2 so for the same Work Request he will put in -2 hours for straight time.

                                                        • Re: Actual Hours Worked versus Hours Available
                                                          George Beaton

                                                          This would work for negative hours too as the equation would give a negative percentage, so

                                                           

                                                          0.25

                                                          0.25

                                                          0.25

                                                          -0.25

                                                          3

                                                           

                                                          Would still sum to 8 for the mechanic's time for that day.

                                                          At the risk of complicating things, you could speed up your script a little by putting the

                                                          [HOURS_STRAIGHT]/ApplyMap('Map_CFtoHoursAvailable', CF_ID) as PercentTimeRecorded 

                                                          line straight into the first and main load of your data.  As long as you have your mapping load before you call the applymap, this would work, and mean you don't have to do a second load from resident, followed by the drop table.

                                                           

                                                          Either way will work though.

                                                           

                                                          George