Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
piekepotloed
Contributor II
Contributor II

15 periods year needed

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

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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;
;

View solution in original post

5 Replies
Vegar
MVP
MVP

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)'
;

 

piekepotloed
Contributor II
Contributor II
Author

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

Vegar
MVP
MVP

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?

Vegar
MVP
MVP

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;
;
piekepotloed
Contributor II
Contributor II
Author

Hi Vegar,

Thanks...it works, now I'm trying to understand what you did 🙂

Peter