Fiscal years

[Dolly123]

I have year 2020,2021,2022 calendar year

and i want FY 2020-2021,

                       FY 2021-2022

                       FY 2022-2023

i want to show in filters and also all filters according to fiscal month,fiscal quarter how we can do this ?

[ogster1974]

this will show you how to add it to your master calendar.

https://community.qlik.com/t5/Design/Fiscal-Year/ba-p/1472103

[vikasmahajan]

TEMP:
LOAD Max("Posting Date") AS MaxDate
from [lib://3_Qvds/SalesData.qvd] (qvd);
//where Date(TranDate,'YYYY-MM-DD') <= Date('2017-11-30', 'YYYY-MM-DD');
LET varMaxDate = Num(peek('MaxDate', 0, 'TEMP'));

TEMP1:
LOAD Min("Posting Date") AS MinDate
from [lib://3_Qvds/SalesData.qvd](qvd);

LET varMinDate = Num(peek('MinDate', 0, 'TEMP1'));

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************

MasterCalendar:
LOAD TempDate AS [Posting Date],
TempDate,
TempDate AS %Date,
YearName(TempDate,0,4) AS FinancialYear,
Date(Yearstart(TempDate,0,4)) AS YearStart,
Date(Yearend(TempDate,0,4)) AS YearEnd,
Num(Date(Yearstart(TempDate,0,4),'YYYY')) AS FinancialYearNo,
date(MonthStart(TempDate),'MMM-YY') AS MonthYear,
Month(TempDate) AS MonthName,
If(Num(Month(TempDate))>3,Num(Month(TempDate))-3,Num(Month(TempDate))+9) AS MonthNumber,
MonthStart(TempDate) AS MonthStart,
MonthEnd(TempDate) AS MonthEnd,
Year(TempDate) AS Year,
'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) AS Quarter,
quarterstart(TempDate,0,4) AS QuarterStart,
quarterend(TempDate,0,4) AS QuarterEnd,
quartername(TempDate,0,4) AS QuarterName,
if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate)) AS FiscalYear,
if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate))-1 as P_FiscalYear ,
AutoNumber(Month(TempDate) & Year(TempDate)) AS Sequential,
NUM(TempDate) AS DateNum,
monthname(TempDate) AS CalMonthYear,
Day(TempDate) &'-'& Date(TempDate,'MMM') AS DayMon,
num(Year(TempDate)&num(Month(TempDate),'00')) as YEAR_MONTH, // Link between tables for previous month / year selection
num((Year(TempDate)-1)&num(Month(TempDate),'00')) AS YEAR_MONTH_PREVIOUS, // Link between tables for previous month / year selection
num((Year(TempDate)-2)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS2,
num((Year(TempDate)-3)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS3,
num((Year(TempDate)-4)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS4,
num((Year(TempDate)-5)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS5,
Date(TempDate,'MMM') &'-'& Date(TempDate,'YY') as YearMonth,
//quarterstart(TempDate,0,4) &'-'& Date(TempDate,'YY') as YearQtr
'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) &'-'&right(Date(TempDate,'YY'),2) as YearQtr,
'FY' & YearName(TempDate,0,4) as ChartFinYear
RESIDENT TempCalendar
ORDER BY TempDate Asc;

DROP Table TempCalendar;
DROP Table TEMP;
DROP Table TEMP1;

Hope this calendar will help you.