Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Wow that posted funny. Sorry about that.
Lets try....
//********************* 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)+IterNo()-1 AS Num,
Date(($(vMinDate)+IterNo()-1),'M/D/YYYY') AS TempDate
autogenerate 1
WHILE $(vMinDate)+IterNo()-1<=$(vMaxDate);
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;