topic Re: Calendar - previous working day in QlikView

Calendar - previous working day

shane_spencer — Sat, 16 Nov 2024 21:47:22 GMT

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

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.

Re: Calendar - previous working day

marcus_sommer — Mon, 03 Dec 2018 12:43:38 GMT

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

Re: Calendar - previous working day

shane_spencer — Mon, 03 Dec 2018 14:22:15 GMT

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!