Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I need a year that starts on the first of October and ends on the 31th Decemer the next year, so there have to be 15 months in the year. Each year should start on the 1st of october and end on the 31th of december, so;
2017/2018 is from 1-10-2017 untill 31-12-2018
2018/2019 is from 1-10-2018 untill 31-12-2019
2019/2020 is from 1-10-2019 untill 31-12-2020
I 've managed to start the year on the 1st of October, and ending it on 30th September, using this formular:
if(Month(tmpDate) > 9, Month(tmpDate) - 9, Month(tmpDate) + 3) as Period
How can I add the three remaining periods?
Thanks,
Peter
Try something like this. In this script I renamed the year you are looking for to [Fiscal year].
LET vL.StartDate = yearstart(today(),-3);
LET vL.EndDate = today();
LET vL.YearLength = 15;
Calendar:
LOAD
%date,
Dayname(%date) as Date,
Month(%date) as Month,
MonthName(%date) as YearMonth,
monthstart(%date) as %period,
YearName(%date) as Year
;
LOAD
date('$(vL.StartDate)'+recno()-1) as %date
AutoGenerate
'$(vL.EndDate)'-'$(vL.StartDate)'
;
[Fiscal year]:
LOAD
%period,
YearName(%period,0,10) as [Fiscal year],
Month as [Fiscal month]
Resident Calendar;
concatenate LOAD
%period,
YearName(YearMonth,-1,10) as [Fiscal year],
Dual(Month, Month-12) as [Fiscal month]
Resident Calendar
WHERE
Month >= 10;
;
If you want 15 month years then you could create a master calendar containing the Year definition below. OBS: it is important that the calendar is generated chronological starting with the oldest periods ending with the newest because of the use of Autonumbering in the Year field generation.
LET vL.StartDate = yearstart(today(),-3);
LET vL.EndDate = today();
LET vL.YearLength = 15;
LOAD
%date,
Dayname(%date) as Date,
MonthName(%date) as YearMonth,
year('$(vL.StartDate)') + Div(Autonumber(MonthName(%date),'YearMonth')-1, $(vL.YearLength)) as Year
;
LOAD
date('$(vL.StartDate)'+recno()-1) as %date
AutoGenerate
'$(vL.EndDate)'-'$(vL.StartDate)'
;
This is indeed the solution for my question, but now i see i forget one important piece in it....
Each year should start on the 1st of october and end on the 31th of december, so;
2017/2018 is from 1-10-2017 untill 31-12-2018
2018/2019 is from 1-10-2018 untill 31-12-2019
2019/2020 is from 1-10-2019 untill 31-12-2020
Sorry for the inconvenience
Peter
The output of this is that some months (oct-dec) are associated with two years and some with one year.
E.g.
1-12-2018 is associated with both "2017/ 2018" and "2018/2019".
Is this a correct understanding?
Try something like this. In this script I renamed the year you are looking for to [Fiscal year].
LET vL.StartDate = yearstart(today(),-3);
LET vL.EndDate = today();
LET vL.YearLength = 15;
Calendar:
LOAD
%date,
Dayname(%date) as Date,
Month(%date) as Month,
MonthName(%date) as YearMonth,
monthstart(%date) as %period,
YearName(%date) as Year
;
LOAD
date('$(vL.StartDate)'+recno()-1) as %date
AutoGenerate
'$(vL.EndDate)'-'$(vL.StartDate)'
;
[Fiscal year]:
LOAD
%period,
YearName(%period,0,10) as [Fiscal year],
Month as [Fiscal month]
Resident Calendar;
concatenate LOAD
%period,
YearName(YearMonth,-1,10) as [Fiscal year],
Dual(Month, Month-12) as [Fiscal month]
Resident Calendar
WHERE
Month >= 10;
;
Hi Vegar,
Thanks...it works, now I'm trying to understand what you did 🙂
Peter