Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
shane_spencer
Valued Contributor

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:

masterdates.PNGMaster 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:

deadlines.PNGDeadlines

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

Re: Calendar - previous working day

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

2 Replies
MVP & Luminary
MVP & Luminary

Re: Calendar - previous working day

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
Valued Contributor

Re: Calendar - previous working day

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!