I am trying to convert my dates to be used on a fiscal calendar (starting July 1st) instead of a standard calendar (Start Jan. 1st):
I usually use this code to set up my master caledar: ( have added fiscal year, which works but i do not know how to change the quarter and the months to show the correct dates)
Any ideas?[8-|]
//********************* Quarters ************************
QuartersMap:
MAPPING
LOAD
rowno
() as Month,
'Q'
& ceil((rowno()/3)) as Quarter
AUTOGENERATE(12);
LET
vMaxDate = CurrentDate+10
;
Let
vMinDate = ReportStartDate-0
;
LET
v7days = today()-7
;
LET
vNxt7days = today()+8
;
LET
v14days = today()-14
;
LET
v21days = today()-21
;
LET
vNxt14days = today()+15
;
LET
v30days = today()-30
;
LET
vNxt30days = today()+30
;
LET
v60days = today()-60
;
LET
vToday = today()
;
LET
vLastYear = makedate(year(today())-1,month(today()),day(today()))
;
LET
v30daysLastYear = (makedate(year(today())-1,month(today()),day(today())))-30
;
LET
vNxt30daysLastYear = (makedate(year(today())-1,month(today()),day(today())))+30
;
//********************* Temporary Calendar *********************
TempCalendar:
UnQualify
*;
LOAD
$(vMinDate)
Date(($(vMinDate)+IterNo()-1),'M/D/YYYY') AS TempDate
autogenerate
MasterCalendar:
LOAD
date
(TempDate,'M/D/YYYY') AS OrderDate,
date
(yearstart(TempDate,0,7),'YYYY') as FiscalYear,
date
(yearstart(TempDate,0,7),'MMM-YY') as FiscalMonth,
date
(TempDate) AS FullDate,
if
(right(weekname(TempDate, 0, -1),2) = 01 AND year(TempDate) < year (weekend(TempDate, 0, -1)) ,
53, right(weekname(TempDate, 0,-1),2)) AS Week'
if(weekday(yearstart(TempDate))='Mon' or 'Sun',if(month(TempDate)='Dec',if(week(TempDate)=1,53,week(TempDate)),week(TempDate)),if((week(TempDate)-1)=0,53,week(TempDate)-1)) AS WeekofYear,
weekstart
(TempDate, 0, -1) & ' - ' & weekend(TempDate, 0, -1) AS WkRange,
weekstart
(TempDate, 0, -1) AS WkStart,
weekend
(TempDate, 0, -1) AS WkEnd'
month
(TempDate) AS Month,,
date
(TempDate,'MMM-YY') AS MonthYear,
day
(TempDate) AS Day,
num
(month(TempDate)) & '/' & day(TempDate) AS MonthDay,
dayname
(TempDate) AS DayName,
weekday
(TempDate) AS Weekday,
year
(TempDate) AS Year,
applymap
('QuartersMap', num(month(TempDate)), null()) AS Quarter,
applymap
('QuartersMap', num(month(TempDate)), null()) & ' - ' & year(TempDate) AS QuarterYear
RESIDENT
TempCalendar;
DROP
TABLE
TempCalendar;
Any ideas
1
WHILE$(vMinDate)+IterNo()-1<=
$(vMaxDate);+IterNo()-1
AS Num'
