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.