topic Re: Calculating Next Business Date in QlikView

Calculating Next Business Date

dafnis14 — Thu, 11 May 2017 10:23:25 GMT

Hi,

I have seen quite a few discussions about the topic, but seems I need a different solution..

I have created a list of Notworking days (including Fridays & Saturdays ).

To each date, I would like to join its next business date.

I created a mapping table with the business days.

and then a loop to generate dates for each not working date, until the next business date is found.

My script is:

Map_BussiDate:
Mapping LOAD CalendDate as Map_BussiDate,
1 as Flag_Bussi_Date
Resident Calend
Where Flag_BusinessDay =1;

NextBusiidate:
LOAD HoliDate as NewHolidate,
date(HoliDate+IterNo()) as NextBussiDate,
ApplyMap('Map_BussiDate', date(HoliDate+IterNo()),0) as Flag_Bussi_Date
Resident SpecialDays
While ApplyMap('Map_BussiDate', date(HoliDate+IterNo()),0)=0;

But the loop stops without applying the mapped business date..

Can someone help me?

Thanks!

Re: Calculating Next Business Date

swuehl — Thu, 11 May 2017 10:32:24 GMT

I would calculate the next business date, something like

LastWorkDate(HoliDate+1, 1, $(ListOfHolidays) )

See the HELP for more information about LastWorkDate(). ListOfHolidays is a variable containing the holidays, comma separated.

Re: Calculating Next Business Date

dafnis14 — Thu, 11 May 2017 10:52:18 GMT

Hi Stefan,

My Weekends are Fridays & Saturdays.

There are holidays which run on consecutive dates.

Is this function suitable for such cases?

Thanks!

Re: Calculating Next Business Date

vishsaggi — Thu, 11 May 2017 11:44:36 GMT

May be this might help?

Week Start - Sunday instead of Monday

Re: Calculating Next Business Date

dafnis14 — Thu, 11 May 2017 11:50:33 GMT

It worked for me only after adding the weekend dates to the ListOfHoliday string.

Not sure if this is the only way to achieve it when you have Sunday-Thursday as working days.

Thanks anyway!

Re: Calculating Next Business Date

swuehl — Thu, 11 May 2017 11:59:15 GMT

Maybe like

Calendar:

LOAD *, Rangesum(WorkingdayFlag, Peek(WorkingdayCounter)) as WorkingdayCounter;

LOAD *, Weekday(Date) as Weekdate, if(Weekday(Date) <4 or Weekday(Date)>5,1,0) as WorkingdayFlag;

LOAD Date(Makedate(2017)+Recno()-1) as Date

AutoGenerate 100;

LEFT JOIN

LOAD Minstring(Date) as NextWorkingDate, WorkingdayCounter-1 as WorkingdayCounter

Resident Calendar Group by WorkingdayCounter;

Re: Calculating Next Business Date

dafnis14 — Thu, 11 May 2017 13:45:45 GMT

Hi Stefan,

Much appreciated!

Sorry for being ignorant..

But I'm not sure how it is linked to the holidays..

As looks like this script doesn't take them into account..

Re: Calculating Next Business Date

swuehl — Thu, 11 May 2017 14:18:12 GMT

As long as you create a table with a WorkingdayFlag, this should work.

As I understood your post, that's what you already have done.

The WorkingdayFlag should show zero for holidays.

Re: Calculating Next Business Date

dafnis14 — Thu, 11 May 2017 16:47:46 GMT

Got it!

Thanks!