Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Actual Hours Worked versus Hours Available

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!

1 Solution

Accepted Solutions
gsbeaton
Contributor II

Re: Actual Hours Worked versus Hours Available

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

17 Replies
Not applicable

Re: Actual Hours Worked versus Hours Available

Hello Casey,

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

,Hannah

Not applicable

Re: Actual Hours Worked versus Hours Available

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
Contributor III

Re: Actual Hours Worked versus Hours Available

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
Contributor II

Re: Actual Hours Worked versus Hours Available

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

Not applicable

Re: Actual Hours Worked versus Hours Available

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.

Not applicable

Re: Actual Hours Worked versus Hours Available

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
Contributor II

Re: Actual Hours Worked versus Hours Available

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

Not applicable

Re: Actual Hours Worked versus Hours Available

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
Contributor III

Re: Actual Hours Worked versus Hours Available

Instead you can provide your Column