Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have load the holiday via Excel
tmpHoliday:
LOAD
Holiday,
Holiday_Date,
Date(Holiday_Date, 'yyyy/MM/dd') as Date
FROM "lib://ProjectX/Holidayfolder/Holiday.xlsx"
(ooxml, embedded labels, table is Holiday);
tmpConcat:
NoConcatenate
LOAD Chr(39) & Concat(Date,Chr(39)&','&Chr(39))&Chr(39) as HolidayDates
RESIDENT tmpHoliday;
LET vPublicHolidays = Peek('HolidayDates',0,'tmpHoliday');
In the expression I uses this
=networkdays (Open_DATE, Close_DATE,$(vPublicHolidays)) - 1
I am able to get the difference between the Open_date and Close_date as well as weekend days removed.
But the Public Holiday failed to be removed from the difference.
What can be wrong?
You need to check the content of your variable vPublicHolidays. It should look something like
'2020/05/01', '2020/12/25', '2020/12/26'
You should also make sure that it is in the same format as your system date format. How does your declaration of variable DateFormat look like? is it SET DateFormat='YYYY/MM/DD';?
You need to check the content of your variable vPublicHolidays. It should look something like
'2020/05/01', '2020/12/25', '2020/12/26'
You should also make sure that it is in the same format as your system date format. How does your declaration of variable DateFormat look like? is it SET DateFormat='YYYY/MM/DD';?
Realize the date format is different
SET DateFormat='DD.MMM.YYYY';
So I have change the loading
LOAD
Holiday,
Holiday_Date,
Date(Holiday_Date, 'DD.MMM.YYYY') as Date
FROM "lib://ProjectX/Holidayfolder/Holiday.xlsx"
(ooxml, embedded labels, table is Holiday);
And is working now! Thanks