How to use Next working day, including weekends in Qlik View

[fabio]

Hi guys!

i need to calculate the next working day from a date X.

I can't use the LastWorkDate function because i need to consider saturday and sunday as working days.

I have a variabile $HolidayList containing all the non-working days. I have to exclude only the days contained in this variable.

Any idea?

Thanks!!

[Andrei_Cusnir]

Hello,

I have a workaround that might help you achieve your use case scenario, however please keep in mind that this might not be 100% solution to your issue. In any case, I hope that this information will help you proceed further with your development.

First of all we need to address the following issue:

If you get a simple date, then the next working day is SELECTED_DATE + 1, which is easy. Also, if you have a list of holidays such as DATE_1, DATE_2, DATE_3, etc. then you just check if the SELECTED_DATE exists in the list and in case it does, you just use SELECTED_DATE + 2.

However, this use case scenario is not working in the event that you have continuous holidays or in another use case scenario such as: 

Imagine that you have the list of holidays: 20/2/2022, 21/2/2022, 26/2/2022 etc. then:

Issue A:

If you select the date 20/2/2022 then the next working date is NOT 21/2/2022, but 22/2/2022.

Issue B:

If you select the date 19/2/2022 then it is NOT part of the list of the holidays, so adding one extra day to the date is NOT going to work, since the next working date is actually 22/2/2022 and NOT 20/2/2022.

Ideal solution:

In that case you need a more advance expression which would contain LOOP statement. Basically, you would check if the selected date is present in the list of holidays and then you would enter a loop where you will start adding days to the SELECTED_DATE. The LOOP will have to check each new date if it is present in the list of holidays and if it is then it will skip it until it finds the date that is not part of the list.

Although the hypothesis above is correct, you can't use LOOP inside the expressions while analyzing your data, which means that this leaves us with the following 2 solutions:

Solution A:

1. In Data load editor, load all the values from your source
2. Create a new table that will hold all the holidays dates and for each date specify which is the next working date
3. In case you want to do this automatically, then you will have to construct the logic with LOOP statement to build the values for next working date

Solution B:

You can hardcode the next working dates in a new variable following the steps below:

1. Create the following variables:

varHolidays => 5/6/2022, 5/7/2022, 8/9/2022

varCheckList => '5/5/2022', '5/6/2022', '5/7/2022', '8/8/2022', '8/9/2022'

varNextWorkingDay =>  '5/8/2022', '5/8/2022', '5/8/2022', '8/10/2022', '8/10/2022'

varSelectedDate => 1/1/2019

NOTE: Some values have '' (Single quotes, which are mandatory for Match() function to work properly)

2. As you can see, I have left the varHolidays variable, just in case you want to use those dates for other purposes and I have created a new variable varCheckList that contains the holidays and the other exceptional dates that we need. 

3. Create a new Text Object with the following expression:

=If(
    Match('$(varSelectedDate)', $(varCheckList)) > 0,
    SubField('$(varNextWorkingDay)', ', ', Match('$(varSelectedDate)', $(varCheckList))),
    Date(Date#('$(varSelectedDate)', 'M/D/YYYY') + 1)
)

Basically, this expression checks if the date value within the selected date variable is included in the Check list. If the selected date is included, it will find the position and will go to the other list with the next working dates, to the same position to get the next working date for the selected date. In case the date is not part of the list, it means that it will just add +1 to the selected date and it will get the next working day (Regardless if it is Sunday, Saturday etc.)

Here are the examples:

Example A: Selecting the date 1/1/2019 that is not part of the check list, will give the next working date as  1/2/2019:

Reason: Because it has added +1 days to the selected date.

Example B: Selecting the date 5/5/2022 it gives the next working date as 5/8/2022.

Reason: Although 5/5/2022 is NOT part of the holidays, but 5/6/2022 and 5/7/2022 is, therefore we found that the corresponding next working day for this date is found in the other list. 

Example C: Selecting the date 5/6/2022 will give the next working date as 5/8/2022

Reason: The date is part of holidays and it is also part of the check list, so we check which is the corresponding next working date.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

[marcus_sommer · Accepted solution]

I suggest to make the essential work within the calendar. Depending on the weekday() and/or any holiday-mapping/generating you could flag the workdays with 1 and the other with 0. For selecting-related evaluations it's enough to sum this, like: sum(FLAG).

For scenarios in which the working days of two unrelated dates should be considered the above sum() might be used twice with an appropriate set analysis and then subtracted and also quite useful could be to accumulate these flag-values from the calendar-start (and maybe also for each year/quarter/month) and to query those values.

Calculating these information within the UI is surely possible but much more complex - especially if this is only a part of a bigger expression it becomes quite unhandy. 

- Marcus

[fabio · Accepted solution]

eureka! I found my solution.

i have a table in the db with the calendar, for each day there is a boolean indicating working day or not (FLAG).

I load it in qlik:

Calendar_tmp:
LOAD

DATE,
FLAG; // 'STOP' indicates a non working day

SQL
select DATE, FLAG from CALENDAR ORDER BY DATE;

Then i load in reverse order the table and i put for each day a column with the next working day using the peek function.

Calendar_P:

LOAD
DATE,
FLAG,
IF (FLAG='STOP',Peek(WorkingDate),DATE) as WorkingDate // Note! it is like a recursion 

Resident Calendar_tmp order by DATE desc ;

Then i can join my facts table with DATE file to obtain the first right working day!!!