Actual Hours Worked versus Hours Available

[#1]

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!

[#2]

Hello Casey,

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

,Hannah

[#3]

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.

[d_prashanthredd]

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.

[gsbeaton · Accepted solution]

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

[#6]

Sorry, I'm a little confused. I already have a table with the available hours each mechanic can work in it (OPS_CF). Plus, not all mechanics only work 8 hour days. Some work 10 (depending on contract). So a hard code of 8 won't work. The OPS CF table lists each mechanic and the hours they are available a day.

[#7]

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?

[gsbeaton]

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

[#9]

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?

[d_prashanthredd]

Instead you can provide your Column