Discussion board where members can learn more about Qlik Sense App Development and Usage.
today i have to calculate the remaining workdays (Monday to Friday) per each months removing the holidays specified on "Holidays" tab of the attached XLSX.
I put also the company code because in my case we have more than one.
Probably i have to use NetWorkDays() but i didn't understand how to specify multiple days dinamically.
So, removing Saturdays and Sundays and the three "Custom Holidays" of the XLSX "Holidays" tab, i wuold like to calculate the two yellow columns and obtain this:
Could you try the test script below:
// Load initial company data
// Note. Match will be based on initial DayNames list from the main tab.
if(match(weekday("Date"),'Sat.','Sun.')>0,0,1) as isAWorkDay
(ooxml, embedded labels, table is CompanyCalendar);
//Add the holidays
left Join (CompanyCal)
Holiday as "Date",
1 as isHoliday
(ooxml, embedded labels, table is Holiday);
if(resetmonth =1, //first day of month
if(isAWorkDay = 1 and isHoliday=0, 1,0),
if(isAWorkDay = 1 and isHoliday=0, peek(daycounter)+1, peek(daycounter)))
,1) as daycounter;
alt(isHoliday,0) as isHoliday,
if(month("Date")<>Previous(month("Date")) ,1,0) as resetmonth
order by Company, "Date" desc;
drop table CompanyCal;
Drop fields resetmonth;
hi i've created a table with three columns:
the first one is the dimension "Date";
the second one contains this measure:
if(Date = '17/10/2018', '0',
if(Date = '1/10/2018', '0',
if(Date = '3/10/2018', '0', 1)))))
the third one contains this measure:
(The bold dates are your holydays.)
This is my result:
your solution works on front end but i need to set up this solution during load script and in a dinamically way (without hard cording dates into formulas) !
The way I see it you would need to evaluate each row based on the Company it belongs to and whether its a holiday for that company. What were you looking at optimising? If you do have a large dataset to load - perhaps you could save the tables as a QVD file as an intermediary step?