Find Number of Holidays between Two Dates in Webi

[former_member595336]

We have two DataSource: 1. SQL 2. Universe

From SQL we fetch data containing Columns Start Date and End Date. We want to find Number of Holidays between Start Date and End Date for all the rows.

To Know the list of Holidays we have universe which has a Date Column and a Column to Mark "Y" for Holiday and "N" for not a holiday.

Note: We want to do the calculation in Webi only and Not at the universe level.

[former_member595336]

Hi Jyothirmayee A,

I Tired Step 2 with "<>" and "=" it is either giving 10 or 0 for Dates value "5/25/2018" and "6/04/2018" when i have a holiday at 5/28/2018.

So it is not respecting the holiday at all :(.

While trying above formula what are your expectations shall i create a join between two data source or keep them independent and just use their columns in formula?

[jyothirmayee_s]

Hi,

As per your statement:

"To Know the list of Holidays we have universe which has a Date Column and a Column to Mark "Y" for Holiday and "N" for not a holiday."

if you are trying to find how many # of holidays between 2 dates., then use step2. with ([your flag object] = "Y").

Thanks,

Jothi

[former_member595336]

Hi Amit,

Thanks for quick reply.

I have seen similar response in many other blog posts but it is not working out for me.

What I tried:

1. Merged two data source on "Start Date" column from SQL and "Date" Column form Universe.

2. Tired to create measure daysbetween([start date];[end date]) where ([Your flag object]<>"Y" as above but it said incompatible operator "<>"

3. Created a "Detail" object from "Holiday Indicator" column assigning merged dimension as dependency and used the new column in formula above. Formula got created successfully but the value is still counting holidays.

4. Created two more "Detail" object from "Start Date" and "End Date" Column and used in above formula along with "Detailed Holiday Indicator" still the values is counting the holidays.

Can you please point out what i might be doing wrong or provide some other alternative. I am stuck on this for a long time. Your earliest reply will be very helpful.

Thanks in advance.

[amitrathi239]

you can use daysbetween() function to calculate days excluding holidays.

e.g.

=daysbetween([start date];[end date]) where ([Your flag object]<>"Y"