I need to translate SAP P10 maintenance hours to a report with specific rules

[former_member710205]

Hi, I need help in WEBI to create a statement with allot of rules. Can anyone help?

Current column data:

Work Hours, Date, Day of Week

The rules:

-any time worked on Sat is 1.5 time with a min of 8 hours regardless of time worked

-any time worked on Sun is 2.0 time with a min of 8 hours regardless of time worked

-any hours worked over 8 during the week is 1.5

-any hours worked on a holiday is 2.0 time with a min of 8 hours regardless of time worked

-Thanks!

[former_member710205]

So I created an excel with holiday dates in a list and imported the query into my report. How would I "Merge" or generate a column that would read:

If "Holiday Date" = "Work Date" then "H" else "Blank"?

[former_member710205]

Thanks so much! Im getting close. How can I create a variable to display specific dates as "H" for holiday like you have above. Im assuming I will need to manualy put these dates in a formula as WEBI wont pull my work holidays?

[ayman_salem]

You should create your holiday calendar and link it to your date.

Example: Create an Excel sheet with your holiday date, create a second query in your report on this Excel sheet, and merge the dates.

[ayman_salem]

Here is the example (taking into account that the data for overtime has already been calculated)

Define the following variables:

WDay: =DayNumberOfWeek([Date])

WDayname: =DayName([Date]) (optional, only for display in the table)

v_WH: =If ([Work Hours]<8 And ([Status] = "H" Or [WDay] = 6 Or [WDay] = 7)) Then 8 Else [Work Hours]

WT: = ( If ([Status]= "H") Then 2*[v_WH] Else ( If [WDay] = 6 Then 1.5*[v_WH] ElseIf [WDay] = 7 Then 2*[v_WH] Else ([v_WH] * 1.5 ) ) )

...

hope this suits your needs

[former_member710205]

as you can imagine, these are manual inputs in the screenshot for examples.

[former_member710205]

that is correct about the Holiday. You will see that there is a 1.5 time on 5/2/20 on a Sat. Its already calculated at OT but we need to revert the hours to represent how many strait time hours that would equate to.

[former_member710205]

See example of desired results. I hope this helps explain what I'm looking for,.

[ayman_salem]

As I can see from your table, this table only shows overtime hours during the week (Monday). mean your data is already calculated overtime (i.e NO NEED to calculate it in the formula ex. worktime=10 ---> 10-8 = 2 hr. Overtime) Is that correct?

Second: Holidays should be in the last row, NOT 5/4/2020. Is that correct?

[former_member710205]

Lets try again

It will be a min of 8 hours only if hours worked is less than 8, more than 8 is calculated.

[ayman_salem]

It is really confusing what you said.

To understand what exactly you want to calculate, take my example and give the expected calculated time. (It has many possible times, and of course you can add sample days)

[former_member710205]

Thanks for the fast response, see screenshot. As you can see, the time and a half and double-time are already separated from the standard first 8 hours.

If weekend work on sat is less than 8 hours, it shall revert to a min of 8*1.5=12, Sunday would revert to a min of 8*2=16. Anything over that is added to the respective 1.5 or 2.0 depending on day. Same applies for Holidays 8*2.0=16.

[ayman_salem]

Where is the screenshot?

Do you mean that the working hours on weekends and on holidays always will be (Sat 8*1.5, Sun 8*2 and holiday 8*2), regardless of the working hours less or more than 8?

if So, then formula "WT" will be:

WT: = ( If ([Status] = "H") Then 2*8 Else ( If [WDay] = 6 Then 1.5*8 ElseIf [WDay] = 7 Then 2*8 Else ([OverTime] * 1.5 + [Work Hours]) ))

Note: "If ([Work Hours] <= 😎 Then [Work Hours] Else" removed

[ayman_salem]

If I understood your request correctly, here is the solution:

Define the following variables:

WDay: =DayNumberOfWeek([Date])

WDayname: =DayName([Date]) (optional, only for display in the table)

OverTime: =If ([Work Hours] > 😎 Then Mod([Work Hours];8) Else 0

WT: =If ([Work Hours] <= 😎 Then [Work Hours] Else ( If ([Status] = "H") Then 2*8 Else ( If [WDay] = 6 Then 1.5*8 ElseIf [WDay] = 7 Then 2*8 Else ([OverTime] * 1.5 + [Work Hours]) ) )

...

Hope this gives you some idea of how to solve your requirement

[ayman_salem]

Can you provide a screenshot of an example data and expected result so that we can properly understand your question and give you the possible correct solution?

* What does "with a min of 8 hours" mean on Saturday, Sunday and holiday? mean it if less than 8 hours are not calculated or the calculation will be with 8 hours or exact the worked time?