Webi Working Day Custom Function

[swoo72]

Dear Experts,

We've been using the daysbetween function in webi for a number of KPI reports on BI4.2.

Our business want us to change these KPI's to show working days between, including national holidays.

I believe this can be achieved using a custom function. Has anyone been able to produce such a solution?

Kind Regards

Simon

[nscheaffer]

You would need a calendar table with the designation of whether a date is a weekend, holiday, or not. Then you could haven an additional query to just return a count between your starting and ending dates.

[krisa24]

Hi,

I tried to play around little bit and If you want to built that in Webi (not in DB) the solution for "working days between" could be as following.

I have spit the calculation to 3 parts:

• number of working days of the start week

[Day of start] = DayNumberOfWeek([Start Date])

[Start week Days] = If [Day of start] = 1 Then 4
ElseIf [Day of start] = 2 Then 3
ElseIf [Day of start] = 3 Then 2
ElseIf [Day of start] = 4 Then 1
Else 0

• number of working days in whole weeks between start and end week

[Whole Week Start] = RelativeDate( [Start Date];7 -DayNumberOfWeek([Start Date]))

[Whole Week End] = RelativeDate( [End Date]; -DayNumberOfWeek([End Date]))

[Whole Weekends] = DaysBetween([Whole Week Start];[Whole Week End]) /7

• number of working days of end week

[Day of End] = DayNumberOfWeek([End Date])

[End week Days] = If [Day of End] <= 5 Then [Day of End]
Else 5

Final calculation would be:

[Working days between] = [Whole Weekends] * 5 + [Start week Days] + [End week Days]

I am not sure for which country you need to apply public holidays, however you can do it also hardcoded way if you know the exact days of the public holidays. Then you would need to compare whether they are fall on working days and whether they are between Start and End date.

However I strongly advise you to create solution on DB level with proper Calendar table as this will be more scalable and easier to maintain.

[swoo72]

Thanks Kristina,

We will give this a try.

Kind Regards

Simon