excluding weekends and public holidays - using both script and expression

[Beez]

Dear all,

Earlier I was excluding only the weekends using the expression as such:

Interval( Floor(NetWorkDays(Min([Reception]), Min([Validation])) - 1) + (frac(Min([Validation])) - frac(Min([Reception]))), 'dd.hh' )

For instance say the reception is: 22.12.2022 15:29:00 and validation is 02.01.2023 13:59:00 I got as a round off result 06 days and 22 hours.

But the 26.12.2022 is monday and its not weekend so I planned to declared the public holidays which I did in script below

 Holidays:
LOAD * INLINE [
Holidays
'01.01.2022'
'18.04.2022'
'01.05.2022'
'09.05.2022'
'26.05.2022'
'06.06.2022'
'23.06.2022'
'15.08.2022'
'01.11.2022'
'25.12.2022'
'26.12.2022'
'01.01.2023'
'10.04.2023'
'01.05.2023'
'09.05.2023'
'18.05.2023'
'29.05.2023'
'23.06.2023'
'15.08.2023'
'01.11.2023'
'25.12.2023'
'26.12.2023'
];

Load
concat(Holidays, ',') as ALL_HOLIDAYS Resident Holidays;

drop table Holidays;

Now I would like to use this ALL_HOLIDAYS in the expression where it exclude weekends and public holidays. For instance I should get 05 days and 22 hours as result instead of 06 days and 22 hours. Would you please guide me.

thanks in advance !

I wrote multiple expressions: 

Interval(
Floor(
NetWorkDays(
Min([Reception]),
Min([Validation]),
$(ALL_HOLIDAYS) // Pass the concatenated holidays as an argument
) - 1
) + (frac(Min([Validation])) - frac(Min([Reception]))),
'dd.hh'
)

Nothing works. Thanks

[Rohan]

Hi @Beez ,

Can you share, what kinda error you are getting ? Like is it some expression related error or what ?

Regards,

Rohan.

[Beez]

For instance if I apply this expression: Interval(
Floor(
NetWorkDays(
Min([Reception]),
Min([Validation]),
$(ALL_HOLIDAYS) // Pass the concatenated holidays as an argument
) - 1
) + (frac(Min([Validation])) - frac(Min([Reception]))),
'dd.hh'
) still I get 6 days and 22 hours not 5 days 22 hours. Thanks Rohan

[Rohan]

Hi @Beez , 

Just do one modification when you are setting the variable for holiday list, 

concat(date(date#(Holidays,'DD.MM.YYYY')), ',')

Try this. I think it is an issue with the date format in the variable. Also try to keep all the date formats consistent. Let me know if it works.

Regards,

Rohan.

[Beez]

Thanks again, I just tried still it doesn't exclude the holidays.

[Beez]

Or is there way where I can directly exclude the holidays directly from the expression: Interval(
Floor(
NetWorkDays(
Min([Reception]),
Min([Validation]),
$(ALL_HOLIDAYS) // Pass the concatenated holidays as an argument
) - 1
) + (frac(Min([Validation])) - frac(Min([Reception]))),
'dd.hh'
)

[Rohan]

Hi @Beez ,

 Can you share a screenshot of the Variable when put in a KPI ? actually i wanna see what is getting set as value in that variable. that will help us debug this further.

Regards,

Rohan.