Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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
MVP & Luminary
MVP & Luminary

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
MVP & Luminary
MVP & Luminary

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

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!