Former Member

# Working day formula that excludes weekends and holidays in WEBI

Hi Guys,

Can we calculate a working day formula that excludes weekends and holidays in WEBI OR in Universe?

The universe I am working on is using stored procedures, so there are no joins or modelling done. Although there is a workday stored proc that I can bring in the universe.

I am just thinking if there is no modelling or joins done in the universe how will this help me in webi?

Thanks,

Jitan

10|10000 characters needed characters exceeded

• May 01, 2014 at 07:18 PM

If you have a date object in universe, you can figure out if it is a Saturday or a Sunday by using following formula:

=If(FormatDate([Date Object];"ddd") InList ("Sat";"Sun");"Weekend";"Working day")

For holidays, you surely need a flag column from your database.

10|10000 characters needed characters exceeded
• Former Member
Aug 12, 2015 at 02:58 PM

Hi Jithan,

This will help you to get Working Days that excludes Weekends between two dates in WebI.

Consider two dates

Purchase Order Date : [PO Date]

Goods Issue Date : [Goods Issue Date]

Formula to capture the Days Between PO and Goods issue Date is as follows:

=DaysBetween(RelativeDate([PO Date];Floor(DayNumberOfWeek([PO Date])/6)*(8-DayNumberOfWeek([PO Date])));RelativeDate([Goods Issue Date];Floor(DayNumberOfWeek([Goods Issue Date])/6)*(8-DayNumberOfWeek([Goods Issue Date]))))-(Floor(DaysBetween(RelativeDate(RelativeDate([PO Date];Floor(DayNumberOfWeek([PO Date])/6)*(8-DayNumberOfWeek([PO Date])));(DayNumberOfWeek(RelativeDate([PO Date];Floor(DayNumberOfWeek([PO Date])/6)*(8-DayNumberOfWeek([PO Date]))))+1)*-1);RelativeDate([Goods Issue Date];Floor(DayNumberOfWeek([Goods Issue Date])/6)*(8-DayNumberOfWeek([Goods Issue Date]))))/7)*2)

Looks Complex?

Just check the below link which explains in detail.

BOBJ Tricks: Workdays Between Two Dates in Webi

Thanks & Regards,

Das

10|10000 characters needed characters exceeded
• Former Member

A less complicated way hopefully....

Considering two dates

Purchase Order Date : [PO Date]

Goods Issue Date : [Goods Issue Date]

First calculate the number of weekends:

[No. of weekends]=DaysBetween(LastDayOfWeek([PO Date]);LastDayOfWeek([Goods Issue Date]))/7

No. of working days theoretically then is Number of days between PO and Goods Issue minus No of weekend days

[No. of working days]=Daysbetween([PO Date];[Goods Issue Date])-(2*[No. of weekends])

FYI.. this does not include holidays. As mentioned above, holidays are virtually impossible in BO

• Former Member
May 02, 2014 at 07:11 AM

Hi Jitan,

In order to have all correctly filled, we created a table in the source system (SAP, DDBB, Excel, etc) where we filled the weekends, and local/national bank days, holidays, etc, it gives you also all the format of dates (number of the week, PK for dates,and all)

Regards

10|10000 characters needed characters exceeded
• Former Member
May 02, 2014 at 09:33 AM

Jitan,

Weekends are easy in Webi, but holidays are virtuallyimpossible without lengthy maintenance.

Holidays will vary by country (e.g. Singapore had a public holiday yesterday, UK has one on Monday)

The only sensible way to do it is via a calendar table in the universe.

Probably not what you wanted to hear but that's the way it is.

10|10000 characters needed characters exceeded
• Former Member

One more suggestion I need from you -

I have a Work_Calendar_VW that has the following columns -

Calendar_Date - 5/1/2014

Calendar_Year - 2014

Calendar_Month - 5

Calendar_Day - 1

Work_Day - 1

Day_Type - WD (Work Day) For Weekends this will be displayed as WE

The columns have all dates for current year in SQL Server.

I am going to pull this into the universe and create 2 derived tables to calculate MTD Day counts.

Derived Table 1 - Calculate Yest Work Day... this will remove all the weekends and holidays and give me the last working day. This includes couple of case statments to check each day if it's a working day or weekend.

For Work Day the above code will be 1 and for weekend 0

Derived Table 2 - This will give me the count of working days in current month using the above view and Derived table 1 Below is the code -

SELECT COUNT(*)

FROM Work_Calendar_VW

WHERE [Work_Day] = 1 AND

(Calendar_Date BETWEEN CAST(CONVERT(VARCHAR(25),MONTH(LastWoringkday()),101) + '/01/'

+ CONVERT(VARCHAR(25),YEAR(LastWoringkday()),101) AS DATE)

AND LastWoringkday()).

I cannot do joins because this universe has been built using stored proc and would like to implement this in BO universe.

let me know if this is the right approach.

Thanks,

Jitan