Last Business Day

[tmumaw]

Hi everyone,

I am trying to figure the last business day of every month.  We work on a 4-4-5 calendar.  Any idea?  Here s the script I am running to get my dates.  Thanks

CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date],
WEDAT,
WKNO
FROM (qvd)
where [Billing Date] = '$(vToday)';

LET vFisYr = peek('FISYR', 0, 'CurrentDate');
LET vFisPd = peek('FISPD', 0, 'CurrentDate');
LET vWkNo  = peek('WKNO', 0, 'CurrentDate');

DROP TABLE CurrentDate;

FiscalCalendar:
LOAD FISPD,
FISYR,
[Billing Date] as [Current Date],
[Billing Date] as [RunDate],
WEDAT as [Week Ending],
WKNO,
WeekDay([Billing Date]) as Weekday,
If(WeekDay([Billing Date]) = 'Sat',1,0) as Wkend,
Day(MonthEnd([Billing Date])) AS DaysInMonth,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1)  AS YTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD  = '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1)  AS MTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD  = '$(vFisPd)' AND WKNO  = '$(vWkNo)', 1)  AS WTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)'  AND WKNO = '$(vWkNo)' -2, 1)  AS L2WTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)'  AND WKNO = '$(vWkNo)' -1, 1)  AS L1WTDFlag,
if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling5)', 1)  AS RTD5Flag,
if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1)  AS RTD4Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1)  AS RTD3Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling2)', 1)  AS RTD2Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling1)', 1)  AS RTD1Flag
FROM (qvd)
WHERE [Billing Date] >= '$(vHistoryDate)'  AND [Billing Date] <= '$(vToday)';

[marcus_sommer]

Do you use a master-calendar like this one: Recipe for a 4-4-5 Calendar or 4-5-4 Master Calendar?

- Marcus

[tmumaw]

My calendar is already broken down to 4-4-5.  I need a way within my script to set a flag telling me it's the last day of my fiscal period (FISPD) in the above script.

[marcus_sommer]

The month are fixed so that an approach like:

-match(DayCounter, 28, 56, 91, ...) as MonthEndFlag

DayCounter is a running Counter within master-calendar.

- Marcus

[tmumaw · Accepted solution]

I got it to work.  Here is what I did.

CurrentDate:

LOAD FISPD,

  FISYR,

  [Billing Date],

  WEDAT,

  WKNO

FROM (qvd)

where [Billing Date] = '$(vToday)';

LET vFisYr = peek('FISYR', 0, 'CurrentDate');

LET vFisPd = peek('FISPD', 0, 'CurrentDate');

LET vWkNo  = peek('WKNO', 0,  'CurrentDate');

DROP TABLE CurrentDate;

FiscalCalendar_Temp:

LOAD FISPD,

  FISYR,

  FISPD & FISYR as PerYr,

  [Billing Date] as [Current Date],

  Num([Billing Date]) as NumCurrDate,

  [Billing Date] as [RunDate],

  WEDAT as [Week Ending],

  WKNO,

  WeekDay([Billing Date]) as Weekday,

  If(WeekDay([Billing Date]) = 'Sat',1,0) as Wkend,

  Day(MonthEnd([Billing Date])) AS DaysInMonth,

  if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1)      AS YTDFlag,

  if([Billing Date] <= '$(vToday)' AND FISPD  = '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1)      AS MTDFlag,

  if([Billing Date] <= '$(vToday)' AND FISPD  = '$(vFisPd)' AND WKNO  = '$(vWkNo)', 1)      AS WTDFlag,

  if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)'  AND WKNO =  '$(vWkNo)' -2, 1)  AS L2WTDFlag,

  if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)'  AND WKNO =  '$(vWkNo)' -1, 1)  AS L1WTDFlag,

  if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling5)', 1)             AS RTD5Flag,

  if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1)             AS RTD4Flag,

  if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1)                 AS RTD3Flag,

  if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling2)', 1)                 AS RTD2Flag,

  if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling1)', 1)                 AS RTD1Flag

FROM (qvd)

     WHERE [Billing Date] >= '$(vHistoryDate)'  AND [Billing Date] <=  '$(vToday)';

BigDate:    

Load

FISYR as FISYRX,

FISPD as FISPDX,

max([Current Date]) as LastDay

Resident FiscalCalendar_Temp group by FISYR, FISPD

;

Left Join (FiscalCalendar_Temp)

Load *,

FISPDX & FISYRX as PerYr,

Date(LastDay,'YYYY-MM-DD') as LastDayMth,

Num(LastDay) as NumLastDayMth

Resident BigDate

;

Drop Table BigDate

;

Drop Fields FISPDX, FISYRX, LastDay;

FiscalCalendar:

Load *,

if(NumCurrDate = NumLastDayMth,1,0) as MthEnd

Resident FiscalCalendar_Temp

;

Drop Table FiscalCalendar_Temp

;