topic Re: Calculating Time Period excluding holidays in QlikView

Calculating Time Period excluding holidays

Wed, 04 Feb 2015 10:31:53 GMT

Hello to all,

I have a simple table that shows the variance between the Desired date to the Goods receipt date. That table is used to measure the performance of a supplier.

Now I want to show the fact that some holidays are not included in the calculation of the variance. I know how to set up the holidays in the script, but I don't know how to use it in the formula.

The formula for the Variance is very simple.

(AVG((DATE(if(isNull(E_BelegPos.Liefertermin),E_BelegPos.Wunschtermin,E_BelegPos.Liefertermin)))-(E_BelegPos.WEDatum)))

COUNT(E_BelegPos.ID)

"Wunschtermin" is the desired date, "WEDatum" the Goods receipt date.

The best example is the first document 251791, here I want to exclude the Christmas Holidays (24.12.,25.12,26.12) and New Year, in total 5 days to exclude.

Thanks for any ideas 🙂

Best

Re: Calculating Time Period excluding holidays

Wed, 04 Feb 2015 10:41:02 GMT

Hi Hannes,

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.

Edit:

Maybe have a look at this thread, might help

http://community.qlik.com/thread/142554

Hope that helps

Joe

Re: Calculating Time Period excluding holidays

Wed, 04 Feb 2015 11:01:38 GMT

Hi Joe,

thanks for the quick reply. I will try and let you know.

Hannes

Re: Calculating Time Period excluding holidays

jonathandienst — Wed, 04 Feb 2015 11:21:35 GMT

Something like this:

In Load script:

Set vHolidays = '2014/12/24', '2014/12/25', '2014/12/26'; // variable contains holiday dates

In expression:

=Avg(NetWorkDays(Alt(E_BelegPos.Liefertermin, E_BelegPos.Liefertermin, E_BelegPos.WEDatum, $(vHolidays))))

/ COUNT(E_BelegPos.ID)

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.

HTH

Jonathan

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.

Re: Calculating Time Period excluding holidays

Wed, 04 Feb 2015 19:36:27 GMT

The networkdate-function works fine (thanks for the tip with the "alt"-function), but a supplier can deliver his goods also on a Saturday. The networkdate excludes the Saturday. Any other ideas?

Re: Calculating Time Period excluding holidays

jonathandienst — Thu, 05 Feb 2015 05:28:45 GMT

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.

Re: Calculating Time Period excluding holidays

Fri, 06 Feb 2015 09:12:10 GMT

Hi Jonathan, as I spoke with our Customer and he told me that a Supplier doesn't deliver on Saturday and Sundays, the networkdate is my 100% choice. Thanks again.

Re: Calculating Time Period excluding holidays

Tue, 17 Feb 2015 10:20:28 GMT

Hey Guys,

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.

Any suggestions?