How i calculate Overtime

[#1]

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.

[sujeetsingh]

Come in more words please

[#3]

come up with little bit clear

[senpradip007]

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.

[#5]

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,

[#6]

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

[djsampat]

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