7 Replies Latest reply: Feb 17, 2015 5:20 AM by Hannes Peiffer RSS

    Calculating Time Period excluding holidays

      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
          Joseph Simmons

          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
            Jonathan Dienst

            HI

             

            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

                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
                    Jonathan Dienst

                    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

                    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

                        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?