Move Date to previous Work day

[johngouws]

Good Morning.
There is a operation that has a production cycle that runs for a day, 8am to 5pm. The outcome, how many of which items that were produced, only gets captured the next WORK day.
Friday's production numbers get captured on Monday etc. The normal procedure is to simply select the actual production date of when production took place. 

With the implementation of a new system this can also be done, BUT, with the new system it is found that one cannot capture to another period. That means production over month end is a problem. Production for 2021-06-30 could not be captured on 2021-07-01.

I would like to overcome the problem in my script. I am already using Networkdays and vPublicHolidays in a Qlik Sales App, in Objects, and would like to find out if I could 'move' the production date to the actual production date by using Networkdays and vPublicHolidays in the script?

With other words, today is Monday. When Admin get to the office today they capture Fridays production numbers under today's date. In my loadscript I 'move' the date to the previous work day. In this case I would move the date from 2021-07-05 to 2021-07-02. If Friday was a holiday it would move the date to 2021-07-01.

I currently list the public holidays as below.
LET vPublic_Holidays = "'2020-01-01', '2020-03-21', '2020-04-10', '2020-04-13', '2020-04-27', '2020-05-01', '2020-06-16', '2020-08-10', '2020-09-24', '2020-12-16', '2020-12-25', '2020-12-26',
'2021-01-01', '2021-03-21', '2021-04-02', '2021-04-05', '2021-04-27', '2021-05-01', '2021-06-16', '2021-08-09', '2021-09-24', '2021-12-16', '2021-12-25', '2021-12-26'";

And the production data come from a QVD where I have

PROD:
Load
*,
NetWorkDays(MonthStart([Date]),MonthEnd([Date]),$(vPublic_Holidays)) as ActualDays,
NetWorkDays(MonthStart([Date]),MonthEnd([Date])) as [Working Days]
FROM [C:\Qlik\Data\Production\Prod.QVD];

** In this case ActualDays and Working Days returns the same result - I also tried**
// NetWorkDays(Date#(MonthStart([DDate]), 'YYYY-MM-DD'), Date#(MonthEnd([DDate]), 'YYYY-MM-DD'),$(vPublic_Holidays)) as ActualDays,
// NetWorkDays(Date#(MonthStart([DDate]), 'YYYY-MM-DD'), Date#(MonthEnd([DDate]), 'YYYY-MM-DD')) as [Working Days]

 I would really appreciate any assistance with this - Thank you - Stay safe..

[tresesco]

May be using FirstWorkDate() (or, lastworkdate()).

[johngouws]

@tresesco , I am going to give it a try during the days and will give feedback. 

🤞

[johngouws]

I have tried a few options using FirstWorkDate() but unfortunately cannot succeed. I am including a qvf with a few notes. 

/* NOTE Friday Monday Wednesday */
LET vPublic_Holidays = "'2021-04-02','2021-04-05', '2021-06-16'";

t1:
LOAD * INLINE [
Date, shouldshow
2021-04-06, 2021-04-01
2021-06-17, 2021-06-15
];

shouldshow are the dates I am expecting to return. 

Thank you for the help. 

[tresesco · Accepted solution]

I don't know why you are using 4 as second parameter to the function, for previous working date, you should use 2 with this function. Also, note the holiday defining variable, try with SET instead, like:

SET vPublic_Holidays = '2021-04-02','2021-04-05', '2021-06-16';

t1:
LOAD * INLINE [
Date, shouldshow
2021-04-06, 2021-04-01
2021-06-17, 2021-06-15
];

Load
*,
firstworkdate ([Date], 2,$(vPublic_Holidays)) as NewDate

[johngouws]

@tresesco . Apologies - the 4 was from one of the tests I was doing. 

Changing the LET to SET did the trick! 😒

Thank you is works perfectly..