topic Re: How i calculate Overtime in QlikView

How i calculate Overtime

Fri, 07 Nov 2014 05:01:58 GMT

Dear All,

I want to overtime calculation Check 1= overtime OK other wise overtime not allow so how i calculate OT*OTRate= overtime.

Note: OT calculate If InTime<8:00 OT Calculate=8:00.

Re: How i calculate Overtime

sujeetsingh — Fri, 07 Nov 2014 05:05:24 GMT

Come in more words please

Re: How i calculate Overtime

Fri, 07 Nov 2014 05:15:50 GMT

come up with little bit clear

Re: How i calculate Overtime

senpradip007 — Fri, 07 Nov 2014 05:18:24 GMT

As per your excel overtime(OT) are bit confusing. Where Onduty is 8hr54min OT is 0 and OT is 2hr58min when onduty is 11hr58min. So your OT calculation is bit confusing.

Re: How i calculate Overtime

Fri, 07 Nov 2014 05:58:14 GMT

Please Sent just logic then i create the Qlikview

Condition:

1) OutTime-InTime= Onduty

2) Onduty-9: Hours= OT

3) IF Check=1 then he have OT

4) OT*OTRate= OTAmount,

Re: How i calculate Overtime

Fri, 07 Nov 2014 07:03:34 GMT

Hi Ala,

If I understand you correctly, please try my script below

LOAD

if([Check] = 1,(interval(interval#(OverTime,'hh:mm'),'hh:mm') * [OtRate]) * 24,'No OT') as OTAmount

;

LOAD

Interval([OnDuty] - [WorkingHrs],'hh:mm') as [OverTime]

;

LOAD

Interval([Out] - [In],'hh:mm') as [OnDuty]

;

LOAD

Check,

time('09:00:00','hh:mm' ) as WorkingHrs,

time(In,'hh:mm' ) as In,

time(Out,'hh:mm' ) as Out,

OtRate

;

LOAD * INLINE

[Check,In,Out,OtRate

1,05:30:00,20:00:00,5

0,05:30:00,18:00:00,8

1,09:00:00,19:00:00,8

];

-Charles

Re: How i calculate Overtime

djsampat — Fri, 07 Nov 2014 07:10:54 GMT

Hi,

Try this

Data:

LOAD *,

interval(if(Check, rangemax(0,Time-3/8),0)) as OverTime,

interval(rangemin(3/8,Time)) as RegularTime;

LOAD *, interval(frac(OutTime) - frac(InTime)) as Time;

LOAD

FROM

[OvertimeCalc.xlsx]

(ooxml, embedded labels, table is TB01);

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

Regards

Dhruv