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: 
shane_spencer
Specialist
Specialist

Calendar - previous working day

I've got a master calendar that includes weekends, public holidays and other special days where no-one will be in the office. I've also got a list of deadlines. If the deadlines is on a non-working day, I need to find the previous working day. n.b. I need to do this in the script (not visually in a chart). 

As you can see the Working Day No is sequential. The flag for Working day is 1 and 0 being non-working day:

Master DatesMaster Dates

I've got this list of Deadlines. The ones in yellow are actually non-working days, so the actual deadline is the previous working day. Therefore I needs a new column/field adding on to this table to show the ACTUAL_PAYMENT_DATE that checks if the PAYMENT_DATE is a non-working day, and if so finds the previous working day:

DeadlinesDeadlines

Can anyone suggest how I do this? I suspect it's quite simple but I just don't know the answer.

n.b. The attached datemaster.xlsx is actually a QVD but the forum would not allow me to upload so I converted it to xlsx.

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer

I think you could use lastworkdate() within the deadlist-table and adjust with it the date-value and creates a mapping-table from it and then pulling these data with applymap() into your calendar.

- Marcus

View solution in original post

2 Replies
marcus_sommer

I think you could use lastworkdate() within the deadlist-table and adjust with it the date-value and creates a mapping-table from it and then pulling these data with applymap() into your calendar.

- Marcus

shane_spencer
Specialist
Specialist
Author

I actually wanted Firstworkdate but that was enough to point me in the right direction:

Firstworkdate(Num(PAYMENT_DATE),1,$(vHoliday_GB)) as ACTUAL_PAYMENT_DATE

I'd already got my variable for Holidays to create my masterdates calendar so could easily re-use it here. Thanks!