Working day formula that excludes weekends and holidays in WEBI

[Former Member]

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?

Your suggestions will be very helpful.

Thanks,

Jitan

[former_member208292]

Hi,
I have 4 date objects D1,D2,D3,D4 in master table and calendar table with Date and Flag (W/H) and need to get the daydifference between (D1,D2),(D1,D3).. excluding the holidays(H).I am using SQL server 2014 and BO 4.2.How to join Calender table and master table and how to use it in webi reports..Highly Appreciated..please

[former_member195061]

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

[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]

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.

[Former Member]

Thanks a lot for your suggestion.

I am going to do this in  Database and then pull it in BO Universe/WEBI.

Thanks again for your help,

Jitan

[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

[former_member208292]

Hi Jitan,
I have 4 date objects D1,D2,D3,D4 in master table and calendar table with Date and Flag (W/H) and need to get the daydifference between (D1,D2),(D1,D3).. excluding the holidays(H).I am using SQL server 2014 and BO 4.2.How to join Calender table and master table and how to use it in webi reports..Highly Appreciated..

[Former Member]

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

[arijit_das]

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.