Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
WHERE [Billing Date] >= '$(vHistoryDate)' AND [Billing Date] <= '$(vToday)';
I got it to work. Here is what I did.
CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date],
WEDAT,
WKNO
FROM
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
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
;
Do you use a master-calendar like this one: Recipe for a 4-4-5 Calendar or 4-5-4 Master Calendar?
- Marcus
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.
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
I got it to work. Here is what I did.
CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date],
WEDAT,
WKNO
FROM
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
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
;