Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
piekepotloed
Contributor
Contributor

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

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;
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
Vegar
Partner
Partner

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
piekepotloed
Contributor
Contributor
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
Partner
Partner

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?

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Vegar
Partner
Partner

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;
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

piekepotloed
Contributor
Contributor
Author

Hi Vegar,

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

Peter