Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sagarwal16
Partner - Contributor II
Partner - Contributor II

Exclude Sat sun and holidays

Hi All,

please let me know how to exclude Sat, Sun and holidays where i am having Id and Due Date.

Add buffer of 5 days in Due Date.

ID      Due Date

C01  5th feb 2019

c02   12th feb 2019

 

Thanks in advance.

6 Replies
Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

Try - Networkdays()
sagarwal16
Partner - Contributor II
Partner - Contributor II
Author

Networkdays() needs end date..and in the requirement we dont have the end date . 

Gysbert_Wassenaar

Where do you want to exclude saturdays, sundays and holidays from?


talk is cheap, supply exceeds demand
tresesco
MVP
MVP

You probably need, LastWorkDate()/FirstWorkDate()

Vegar
MVP
MVP

I'm not sure if I understand completely your issue, but maybe this might help you.
Load
ID ,
[Due Date],
lastworkdate( [Due Date], 1) as DueWorkDate
FROM ....

You can adjust the lastworkdate() with a list of holidays as extra parameter in the end. Read about it here.
https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...
HirisH_V7
Master
Master

Hi Check the below code:

This may help with your requirement, 

Temp:
Load ID+IterNo() as ID,
     Date(Date+IterNo()) as Date,
     WeekDay(Date+IterNo()) as WeekDay
     While IterNo()<=10;
Load 1 as ID,
     '01-06-19' as Date AutoGenerate 1;
     
Holiday_Calendar:
left join
LOAD * ,
1 AS IsHolidayFlag

INLINE [
    Date, Description 
    05-06-19, Opt1
    04-06-19, Opt2
];

Data:
	Load ID,
	     Date,
	     WeekDay,
	If(SubStringCount('$(vWeeklyHolidays)', WeekDay(Date)) OR IsHolidayFlag = 1, 1, 0) AS IsHolidayFlag,
	  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(Date)) OR IsHolidayFlag = 1,'Non Working/Holiday', 'Working Day') AS DayType
Resident Temp;
DROP Table Temp;

 With some sample data , created some flags for holidays. 

 

PFA APP for ref. let us know if  missing anything.

 

 

HirisH
“Aspire to Inspire before we Expire!”