Have a look at the networkdays() function. You can pass your starting date, ending days and a list holiday dates to exclude into it.
This function only counts the number of working days (Mon-Fri), you should be able to use this function twice, once with the holidays, second time without and take the resulting difference as the number of days you need to exclude.
Maybe have a look at this thread, might help
Hope that helps
Something like this:
In Load script:
Set vHolidays = '2014/12/24', '2014/12/25', '2014/12/26'; // variable contains holiday dates
=Avg(NetWorkDays(Alt(E_BelegPos.Liefertermin, E_BelegPos.Liefertermin, E_BelegPos.WEDatum, $(vHolidays))))
Note that the SET statement must have quote delimited, comma separate list of dates in the correct date format for your system. You can construct the vHolidays array by using a spreadsheet containing holidays or linking to www.timeanddate.com. I use a variable rather than hard coding the holidays into the expression.
Alt() function returns the first parameter that is not null and a valid number. Dates are numbers. More compact and possibly more efficient than the if(IsNull()...) method.
That depends on whether you want to exclude or include the Saturday in the metric. Its going to be awkward to include Saturdays if the delivery is on Saturday and exclude them otherwise. You might want to calculate workdays between E_BelegPos.Liefertermin and E_BelegPos.WEDatum during loading (at an order line level), and then average this calculated amount for the metric.
I have to come up again with this issue:
The formula for my delivery in time-KPI is as follows:
IF(date(date#(E_BelegPos.Wunschtermin,'DD/MM/YY'))- date(date#(E_BelegPos.CALC_Liefertreue_Termin,'DD/MM/YY')) <= 5 and
date(date#(E_BelegPos.Wunschtermin,'DD/MM/YY'))- date(date#(E_BelegPos.CALC_Liefertreue_Termin,'DD/MM/YY')) >= -1
The "Wunschtermin" is the date that I wish the goods to be accepted by the supplier.
The "CALC_Liefertreue_Termin" is the real goods receipt date,
We set all goods that come in 5 days before the "Wunschtermin" and one day after the "Wunschtermin" as IN TIME.
Now I have the challenge hat I would like to exlude the Saturday and the Sunday.
The NetWorkDays-Function dosen't work for the early deliveries. I also tried to flag the Saturday and the Sunday, but this also doesn't work.