Adding days: workdays, weekends, holidays

[#1]

Hello!

I have to add work days to the existing dates and should also keep in mid possible holidays and weekend. I have a separate table with country specific calendars with each day of the year having a value in a separate column to indicate working and non-working day. The table looks like this:

1 - weekend/holiday

0 - working day

Currently I have weekend and holiday information available for a several countries and for several years.

The idea is to add a certain amount of days to the date column and if calculated date falls on the weekend or holiday transfer it to the next working day. I created a column with non-working days as "1" so that it helps me to calculate by adding necessary amount of day to the final date. But I know there is a way of adding working days with LastWorkDate(StartDate, NumberOfDays) but this only considers weekends.

My current formulas is LastWorkDate(<date>,<deliveryDays>) in the dashboard. I plan to have this implemented in the script side later.

<date> retrieves specific date and <deliveryDays> retrieves a number of days(int) for each entry.

Each entry has a country information and thus link to the calendar table shown below(originally TFACS table from SAP).

With NetWorkDays() I can also use holidays information but here I am not sure how to do it properly.  I am quite new to QlikView and to scripting in general and this seems a bit tricky. Maybe someone can propose any idea?

[Gysbert_Wassenaar]

The LastWorkDate function also accepts a list of dates as holidays, just as the networkdays function

From the help file:

lastworkdate(start_date, no_of_workdays {, holiday})

Returns the earliest ending date to achieve number_of_workdays (Monday-Friday) if starting at start_date taking into account any optionally listed holidays. Start_date and holiday should be valid dates or timestamps.

Examples:

lastworkdate ('2007-02-19', 9) returns '2007-03-01' 

lastworkdate ('2006-12-18', 8, '2006-12-25', '2006-12-26') returns '2006-12-29' 

[#3]

I actually realized that LastWorkDate() is not exactly what I need. It returns the last working day of the week where my calculation ends. That means, if the calculated date falls on Saturday LastWorkDate() returns Friday's date. It always returns previous working day if it falls on the same week.

With a bit of experimenting I get this:

An example above:

Note: 'Holiday Table' consist of both weekends and holidays. '2015-04-04' is Saturday.

In the "Calc.Date" I add 3 days to the original date. After adding 3 days '2015-04-01' becomes '2015-04-04' which is Saturday. It returns '2015-04-03'(Last working day = Friday). I need to return the next working day which is Monday '2015-04-06'.

[Gysbert_Wassenaar]

Sounds like you need the FirstWorkDate function then.

[#5]

I want to add days to the original date while FirstWorkDate substracts days from the specified end date (which I don't know and need to calculate).

[Gysbert_Wassenaar]

Uhm, right. My mistake. But LastWorkDate should return the first date to achieve the number of workdays you specified:

lastworkdate(start_date, no_of_workdays {, holiday})

Returns the earliest ending date to achieve number_of_workdays (Monday-Friday) if starting at start_date taking into account any optionally listed holidays. Start_date and holiday should be valid dates or timestamps.

Are you sure your specifying the correct number of workdays? Are you perhaps subtracting two dates? The difference between two consecutive days is one, but they're still two days. Or to put it another way LastWorkDate(Today(),1) will return today's date.

[#7]

I made it working with the FIrstWorkDate(Orig.Date, DaysToAdd, Holidays) but there is another problem now.

My Holidays calendar table consists of multiple countries and country specific calendars. I store the values of all holidays into a variable but then it retrieves all holidays and uses them in my calculations.

My current script is similar to this:

     //Loading calendar information

    //Calendar_id=Country code

     HolidayCalendar:

     LOAD Calendar_id, 

          DATUM,

          ...

     FROM ...

     //DATUM consists of holidays (each date on a separate row)

     tmpConcat:

     Load Concat(chr(39) & DATUM & chr(39),',' ) as HolidayDates

     Resident HolidayCalendar;

     //Retrieving the values of the holidays and assigning them into a var

     let vPublicHolidays = FieldValue('HolidayDates',1);

The line above stores all holidays despite of the country which is not desirable in my calculation. I was wondering if there is a good way of splitting the countries and storing all country specific holidays separately. I have many countries and many dates.

[Gysbert_Wassenaar]

You could let users select a country and use the concat expression in the front end. That will get the user a list of holidays for the selected country to be used in the FirstWorkDate expression. The alternative is to create a variable for each country in the script by looping through the countries and generating a load statement that retrieves the holidays for that country and stores the list in a variable.

FOR i=1 to FieldValueCount('Country')

     LET vCountry = FieldValue('Country', $(i));

     Temp:

     LOAD

          concat(chr(39) & DATUM & chr(39), ',') as HolidayDates

     RESIDENT

          HolidayCalendar

     WHERE

          Country = '$(vCountry)'

          ;

     LET vPublicHolidays_$(vCountry) = peek('HolidayDate',-1,'Temp');

     DROP TABLE Temp;

NEXT

[#9]

Thank you for your help, Gysbert!

Now I have another question. The script suggested by you works fine and creates separate variables with a list of country specific holidays. Now I would like to know if there any way to use those variables according to the country. Countries are not being selected but in the data table each entry has a country information and new dates should be calculated using the appropriate country-specific holidays.

As in the table above I have 'CN' values in Cal.ID columns(country code). Each entry has a reference to a certain country, e.g. CN, US and others, and I would like to use automatically correct variable in LastWorkDate(start_date, no_of_workdays {, holiday}) formula.

I can think of some kind of partial match. For example, if(Match(Country, $(vCountry)), LastWorkDate(<date>,<daystoadd>, vPublicHolidays&$(vCountry)). I tried to use the Match formula in order to return the list of holidays

=if(Match(Country, vCountry),vPublicHolidays&$(vCountry))

but it works only for 1 value as vCountry is holding only the value for the last loaded holiday calendar. I could trim all vPublicHolidays&$(vCountry) variables and compare each of them to the Country name but that is not practical as there are multiple countries in the list.

[HurikanLorenzo]

In case anybody else tries out  Gysbert's code it works perfect when minor typo with field name HolidayDates is corrected:

FOR i=1 to FieldValueCount('Country')

     LET vCountry = FieldValue('Country', $(i));

     Temp:

     LOAD

          concat(chr(39) & DATUM & chr(39), ',') as HolidayDates

     RESIDENT

          HolidayCalendar

     WHERE

          Country = '$(vCountry)'

          ;

     LET vPublicHolidays_$(vCountry) = peek('HolidayDates',-1,'Temp');

     DROP TABLE Temp;

NEXT