Hi,
I have the below code I use for my Calendar, is there away to change it so it relates to financial year instead of calendar year? Is it best to just add extra fields to the Calendar so I can see the Financial flags as well? Our Financial Year starts between 1st April to 31st March.
[StartDate]:
LOAD min([Date]) as Oldest
FROM
(qvd);
LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),
peek('Oldest')-1,
monthstart(addmonths($(vToday),-13))-1); // the -1 makes loop easier
LET vEndDate = num(yearend(today()));
SET vCompStartDate = monthstart(addmonths(today(),-14));
Calendar:
LOAD Date,
year(Date) as Year,
month(Date) as Month,
monthstart(Date) as [Month Start],
'W'&week(Date) as Week,
month(Date)&'-'&Year(Date) as [Month Year],
'Q'&Ceil(Month(Date)/3) as Quarter,
QuarterName(Date) as [Quarter Name],
day(Date) as Day,
weekday(Date) as Weekday,
weekday(Date)&' '&day(Date) as [Day of Week],
if(Date=$(vToday), 1) as [CD Flag],
// if(Date=($(vToday)-1),1)as [PD Flag],
if(Date=($(vToday)-1),1)as [PD Flag 2],
if(year(Date)=year($(vToday)),1) as [CY Flag],
if(year(Date)=year(addyears($(vToday),-1)),1)as [PY Flag],
If((Date>=YearStart($(vToday)) and Date<=$(vToday)), 1) as [CYTD Flag],
If((Date>=YearStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYTD Flag],
if(QuarterName(Date)=QuarterName($(vToday)),1) as [CQ Flag],
If((Date>=QuarterStart($(vToday)) and Date<=$(vToday)), 1) as [CQTD Flag],
If((Date>=QuarterStart(AddMonths($(vToday),-3)) and Date<=AddMonths($(vToday),-3)), 1) as [PQTD Flag],
If((Date>=QuarterStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYQTD Flag],
If((Date>=MonthStart($(vToday)) and Date<=MonthEnd($(vToday))), 1) as [CM Flag],
If((Date>=MonthStart($(vToday)) and Date<=$(vToday)), 1) as [CMTD Flag],
If((Date>$(vToday) and Date<=MonthEnd($(vToday))), 1) as [RCM Flag],
If((Date>=MonthStart(AddMonths($(vToday),-1)) and Date<=AddMonths($(vToday),-1)), 1) as [PMTD Flag],
If((Date>=MonthStart(AddMonths($(vToday),-1)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [PM Flag],
If((Date>=MonthStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYMTD Flag],
If((Date>=MonthStart(AddMonths($(vToday),-3)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [R3 Flag],
If((Date>=MonthStart(AddMonths($(vToday),-12)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [R12 Flag],
If((Date>=MonthStart(AddMonths($(vOneYearAgo),-3)) and Date<=MonthEnd(AddMonths($(vOneYearAgo),-1))), 1) as [PY R3 Flag],
If((Date>=MonthStart(AddMonths($(vOneYearAgo),-12)) and Date<=MonthEnd(AddMonths($(vOneYearAgo),-1))), 1) as [PY R12 Flag],
If((Date>=WeekStart($(vToday)) and Date<=WeekEnd($(vToday))), 1) as [CW Flag],
If((Date>=WeekStart($(vToday)-7) and Date<=WeekEnd($(vToday)-7)), 1) as [PW Flag],
If((Date>=WeekStart($(vToday)) and Date<=$(vToday)), 1) as [CWTD Flag],
If((Date>$(vToday) and Date<=WeekEnd($(vToday))), 1) as [RCW Flag],
If(Year(Date)>=year(addmonths(today(),-13)),Year(Date)) as [New Year];
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate);
[Analysis Periods]:
LOAD monthstart(addmonths($(vCompStartDate), 1*IterNo())) as [Month Start],
IterNo() as [Analysis Period]
AUTOGENERATE 1
WHILE IterNo() <= 14;
