topic Adding days: workdays, weekends, holidays in QlikView

Adding days: workdays, weekends, holidays

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Adding days: workdays, weekends, holidays

Gysbert_Wassenaar — Mon, 21 Mar 2016 14:31:08 GMT

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'

Re: Adding days: workdays, weekends, holidays

Mon, 21 Mar 2016 15:38:21 GMT

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'.

Re: Adding days: workdays, weekends, holidays

Gysbert_Wassenaar — Mon, 21 Mar 2016 16:32:27 GMT

Sounds like you need the FirstWorkDate function then.

Re: Adding days: workdays, weekends, holidays

Mon, 21 Mar 2016 16:41:14 GMT

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).

Re: Adding days: workdays, weekends, holidays

Gysbert_Wassenaar — Mon, 21 Mar 2016 17:15:46 GMT

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})

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.

Re: Adding days: workdays, weekends, holidays

Thu, 24 Mar 2016 10:44:40 GMT

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.

Re: Adding days: workdays, weekends, holidays

Gysbert_Wassenaar — Thu, 24 Mar 2016 11:30:53 GMT

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;

Re: Adding days: workdays, weekends, holidays

Wed, 30 Mar 2016 08:06:12 GMT

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.

Re: Adding days: workdays, weekends, holidays

HurikanLorenzo — Wed, 08 Jan 2020 00:12:23 GMT

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;