Webi report formula question

[HiShilpiRanjan]

Hi All,

Need your help to solve a query.

I have 4 columns in webi report need calculation as below:

Week no.	Total approved OT hrs	Productive Normal OT allowance	OT allowance	Unpaid Overtime Hrs
week 1	20 hrs	5.5 hrs	12	2.5
01-Oct-23	3 hrs	3	0	0
02-Oct-23	0 hrs	0	0	0
03-Oct-23	3 hrs	2.5	0.5	0
04-Oct-23	4 hrs	0	4	0
05-Oct-23	2 hrs	0	2	0
06-Oct-23	0 hrs	0	0	0
07-Oct-23	8 hrs	0	5.5	2.5
		5.5	12

The "Total approved OT hrs" is the 2nd column whose values are coming from Database.
"Productive Normal OT allowance" 3rd column can be maximum 5.5 hrs each week.
"OT allowance" 4th column can be maximum 12 hrs each week.
Unpaid OT hours will have the rest hrs that is sum(The Total approved OT hrs in every week)-12.

Thank you in advance.

[ayman_salem]

It was a challenge, and here's how to solve it

Define the following variables:

v_WeekNo
=Week([Date])

v_MaxAllowProdNormalOT
=5.5

v_MaxAllowOT
=12

v_tmp1
=Previous([v_MaxAllowProdNormalOT]-RunningSum([Total approved OT hrs]; ([v_WeekNo]));([v_WeekNo]))

v_tmp2
=Previous([v_MaxAllowOT]-RunningSum(([Total approved OT hrs] - [v_AllowProdNormalOT]);([v_WeekNo]));([v_WeekNo]))

v_AllowProdNormalOT
= If(IsNull([v_tmp1])) Then ([Total approved OT hrs]) ElseIf ([Total approved OT hrs] = 0 Or [v_tmp1] < 0) Then 0 ElseIf ([v_tmp1] > [Total approved OT hrs]) Then [Total approved OT hrs] Else [v_tmp1]

v_AllowOT
= If(IsNull([v_tmp2])) Then ([Total approved OT hrs] - [v_AllowProdNormalOT]) ElseIf ([Total approved OT hrs] = 0 Or [v_tmp2] < 0) Then 0 ElseIf ([v_tmp2] > [Total approved OT hrs] - [v_AllowProdNormalOT]) Then ([Total approved OT hrs] - [v_AllowProdNormalOT]) Else [v_tmp2]

v_UnpaidOvertime
=[Total approved OT hrs]-([v_AllowProdNormalOT]+[v_AllowOT])

Note: The table must be sorted by “Date”.

.......

I hope it helps

[HiShilpiRanjan]

Thanks for your help.

[HiShilpiRanjan]

Posting some more details for the same question:
The excel attached has 5 columns.
The left two columns data are coming from Database.
The right 3 columns need to be derived based on logic mentioned below:

Logic for the 3 Derived columns:

1> Productive Normal OT allowance(max 5.5 hrs in a week):
maximum 5.5 hours allowed in a week for a employee

2> OT allowance(max 12 hrs in a week):
maximum 12 hours allowed in a week for a employee

3> Unpaid Overtime hrs:

Lets say
Total approved OT (hrs) in a week (1st Jan to 7th Jan)for a employee =18 hrs
Productive Normal OT allowance(max 5.5 hrs in a week)= 5.5 hrs
OT allowance(max 12 hrs in a week)=12 hrs
Unpaid Overtime hrs= 18-5.5-12 = 0.5 hrs

I will be applying Break on Weeks, however need some way to derive the logic for right 3 columns.

Can anyone please suggest.
Thanks in advance..