Calculating Next Business Date

[dafnis14]

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!

[swuehl · Accepted solution]

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.

[dafnis14]

Hi Stefan,

My Weekends are Fridays & Saturdays.

There are holidays which run on consecutive dates.

Is this function suitable for such cases?

Thanks!

[vishsaggi]

May be this might help?

Week Start - Sunday instead of Monday

[dafnis14]

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!

[swuehl]

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;

[dafnis14]

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

[swuehl]

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.

[dafnis14]

Got it!

Thanks!