Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello.
i have this calendar and i need to let the year start from february (fiscal year).i need to use it in a YTD expression how can i let the year strat from ferury using this calendar???
//set this variable to define years to generate
YearsToGenerate=4;
StartYear = 2005;
//Look through number of years defined above
for i = 1 to YearsToGenerate;
let curYear = StartYear+(i-1);
let StartDate = makedate(curYear,2); //Returns first day of year
let YearEnd = YearEnd(StartDate,0,2); //Returns last day of year
let EndWeek = WeekEnd(StartDate);
let DayEndWeek = day(EndWeek);
let WeekStart = date(EndWeek);
//use if week starts on Saturday let WeekStart = date(EndWeek - 1);
//Calculate number of days in the year
let TotalDays =(YearEnd-StartDate)+2;
//Create a temporary calendar
TempCalendar:
load recno()*$(i) as DateKey,
'$(StartDate)'+recno()-1 as PeriodDate
autogenerate(TotalDays-1);
Next
//Building the master calendar with most date dimensions
MasterClendar:
load PeriodDate,
week(PeriodDate) as Week,
DateKey,
Year(PeriodDate) as Year,
Month(PeriodDate) as Month,
Day(PeriodDate) as Day,
Week(PeriodDate) as CalendarWeek,
Week(PeriodDate)&'-'&Year(PeriodDate) as WeekYear,
weekday(PeriodDate) as WeekDay
resident TempCalendar order by PeriodDate Asc;
Drop table TempCalendar;
In you expression you could do:
YourDateField >= YearStart(Today(), 0, 2)
Or the this if using set analysis.
YourDateField = {'>=$(=YearStart(Today(), 0, 2))'}
This should return only rows where the YourDateField is in the current year starting 1st February.
Jay