Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
dafnis14
Contributor III

Calculating Next Business Date

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Calculating Next Business Date

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.

View solution in original post

8 Replies
MVP
MVP

Re: Calculating Next Business Date

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.

View solution in original post

dafnis14
Contributor III

Re: Calculating Next Business Date

Hi Stefan,

My Weekends are Fridays & Saturdays.

There are holidays which run on consecutive dates.

Is this function suitable for such cases?

Thanks!

vishsaggi
Esteemed Contributor III

Re: Calculating Next Business Date

May be this might help?

Week Start - Sunday instead of Monday

dafnis14
Contributor III

Re: Calculating Next Business Date

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!

MVP
MVP

Re: Calculating Next Business Date

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

Re: Calculating Next Business Date

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

MVP
MVP

Re: Calculating Next Business Date

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

Re: Calculating Next Business Date

Got it!

Thanks!