Calculate the last working day of next month

[annagustafsson]

Hi,

I work with an SLA-applikation and need to calculate the last working day of the next month. Do you have any tips on how I can do that?

// Anna 

[maxgro]

Maybe

FirstWorkDate(MonthEnd(Today(1),1),1)

Today(1)

MonthEnd(Today(1),1) = month end of next month

FirstWorkDate(MonthEnd(Today(1),1),1) = link below 

https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

[annagustafsson]

Thanks for your reply,

How do I take into account if the day falls on a holiday?

[maxgro]

you can use the optional 3rd parameter of FirstWorkDate

from Qlik Help:

firstworkdate ('29/12/2014', 9)	Returns '17/12/2014.
firstworkdate ('29/12/2014', 9, '25/12/2014', '26/12/2014')	Returns 15/12/2014 because a holiday period of two days is taken into account.

[annagustafsson]

What does number 9 stand for?

I want to take into account all the red holidays. So I have created a holiday calendar for this dates. 

[maxgro]

9  is the number of working days before

firstworkdate ('29/12/2014', 9), 9 days before the 29/12/2014

// Example with holidays calendar

Holidays:
LOAD * INLINE [
Holiday
19/12/2021
20/12/2021
21/12/2021
22/12/2021
23/12/2021
24/12/2021
25/12/2021
26/12/2021
27/12/2021
28/12/2021
29/12/2021
];

// make a variable (vHolidays) with the date of holiday calendar

Holidays2:
LOAD chr(39) & CONCAT(Holiday, chr(39) &','& chr(39)) & chr(39) AS Hol RESIDENT Holidays;

LET vHolidays = PEEK('Hol');

LET vDate = MAKEDATE(2021,12,29);

// without holiday calendar

LET v1 = firstworkdate('$(vDate)', 1);

// with holiday calendar (vHolidays)

LET v2 = firstworkdate('$(vDate)', 1, $(vHolidays));

TRACE vHolidays=$(vHolidays);
TRACE vDate=$(vDate) v1=$(v1);
TRACE vDate=$(vDate) v2=$(v2);

In your case it should be

FirstWorkDate(MonthEnd(Today(1),1),1, $(vHolidays));

[annagustafsson]

Thank you so much for your help! I have two questions for you, 

what do I do if I'm going to use it in an If statement. Example: 
if ([SLA 1]> FirstWorkDate (MonthEnd (Today (work_ready), 1), 1), $ (vHolidays)) as flag_work ready? 

'work_ready' = the date the work Is done, so always a timestamp 

And, what do you mean I have to do a "make date"?