Dynamic Calculation for Utilization - Qlik Community

kfir1987 · ‎2018-07-31

Hi,

I am trying to calculate utilization for working hours in a certain period of time. As you know utilization formula is

actual working hours / total hours.

my problem is to calculate the total hours field , because it can be in quarters or years ... another example:

let say I want to calculate the total time for this quarter (Q3 - 2018) but the quarter still not ended (we have two more month to finish the quarter). So the total time (lets calculate it in days) is 31 days.

Of course the utilization should be change based on user selection, in case the user selected Q2 2018 than the user will see the utilization for Q2 2018 but in case Q3 2018 choose than the user will see relative utilization until current moment.

Note: my time frame is minute

So my need is to find a way to calculate dynamic field for total working hours

Thanks

balabhaskarqlik · ‎2018-07-31

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

6/9/18 11:08 AM,6/9/18 2:57 PM

6/18/18 09:20 AM,6/20/18 01:13 PM

6/17/18 09:20 AM,6/20/18 01:13 PM

6/17/18 09:20 AM,6/19/18 01:13 PM

5/27/18 12:41 PM,5/6/18 4:38 PM

5/29/18 4:45 PM,5/6/18 4:19 PM

5/1/18 09:00 AM,5/3/18 10:00 AM

5/3/18 03:00 PM,5/6/18 09:00 AM

];

TMP:

LOAD ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by ID;

drop table TMP;

In addition to the weekends, you can also exclude holidays from the interval calculation, by adding your holidays to the vHol variable (you can create this variable from a holiday table, there are some posts here on how to do this.

I then create a record per date in each interval and assign the start and end times per date.

You need to adjust the timestamp format to your format used, check the format codes in the Help if needed.

Or

Try something like this:

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.

kfir1987 · ‎2018-08-01

Hi,

In your solution you are giving soultion for specific dates, I dont know what time period will be selected.

I need something more generic.

thanks