Create fiscal year calendar in Qliksense

[Aspiring_Developer]

Hello ,

I want to create a fiscal year master calendar and I have used the below script :-

//-- Fiscal year Mastr Calendar---//

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(EMLC_OK_DATE) as minDate,
max(EMLC_OK_DATE) as maxDate
Resident ZZ_FF_BUILD_DETAIL_DAILY;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS EMLC_OK_DATE,
week(TempDate) As RFS_Fiscal_Week,
Year(TempDate) As RFS_Fiscal_Year,
Month(TempDate) As RFS_Foscal_Month,
Day(TempDate) As Day,
// YeartoDate(TempDate)*-1 as CurYTDFlag,
//YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as RFS_Fiscal_MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as RFS_Fiscal_Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as RFS_Fiscal_WeekYear,
QuarterName(AddMonths(TempDate,3)) as RFS_Fiscal_QuarterName,
WeekDay(TempDate) as RFS_Fiscal_WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

exit script;

//==============================//

But I am not getting the proper output. Please see below :-

Not sure, what mistake I made in the above script. Can anyone please help me ?

Thanks in advance

@sunny_talwar 

[Manojkumar_250830 · Accepted solution]

@Aspiring_Developer : Please use below script to get your expected output. (add below code in Master calendar table )

 Left(Year(YearName(TempDate,0,4)),5) & '-'& IF(Month(TempDate)<=3, Month(TempDate)+9, Month(TempDate)-3)  as Fiscal_Month_Year

[Aspiring_Developer]

Hi @Manojkumar_250830 

Thanks for providing the solution . I am able to get the results. However, I am facing some issue with the year filter. Although I do have the data for 2017 but it is not 2017 is not reflecting in my year filter. My minimum date in data in 04/April/2017 . PFB the snapshot:-

Below is script used :-

Temp:
Load
min(EMLC_OK_DATE) as minDate,
max(EMLC_OK_DATE) as maxDate

Resident ZRV_FF_BUILD_DETAIL_DAILY where EMLC_OK_DATE <> '01/01/9999';

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS EMLC_OK_DATE, // Normal Calendar
week(TempDate) As RFS_Week_New, // Normal calendar Week
Year(TempDate) As RFS_Year_New, // Normal Calendar year
Month(TempDate) As RFS_Month_New, // Normal Calendar Month,

// **** below script is converting Normal calendar to Fiscal Year ******///////

IF(Month(TempDate)<=3, Month(TempDate)+9, Month(TempDate)-3) as New_Fiscal_Month_Num,

Dual(Month(TempDate), IF(Month(TempDate)<=3, Month(TempDate)+9, Month(TempDate)-3)) as New_Fiscal_Month,

Year(yearName(TempDate,0,4))+1 as New_Fiscal_year,

Left(Year(YearName(TempDate,0,4)),5) & '-'&Right(Year(YearName(TempDate,0,4))+1,2) as New_Fiscal_Year_name,

IF(Match(Num(MOnth(TempDate)),'4','5','6'), 'Q1', IF(Match(Num(MOnth(TempDate)),'7','8','9'), 'Q2',

If(Match(Num(Month(TempDate)),'10,11','12'),'Q3 ', If(Match(Num(Month(TempDate)),'1,2','3'),'Q4')))) as New_Fiscal_Quater,

Left(Year(YearName(TempDate,0,4)),5) & '-'& IF(Month(TempDate)<=3, Month(TempDate)+9, Month(TempDate)-3) as Fiscal_Month_Year

Resident TempCalendar

Order By TempDate ;

Drop Table TempCalendar;

Thanks You

[Manojkumar_250830]

@Aspiring_Developer : If you have data starting from 01-Apr-2016 to 31-03-2017 then the fiscal year for this period is 2017 and If you have data starting from 01-Apr-2017 to 31-03-2018 then the fiscal year for this period is 2018 however, If you have data starting from 01-Apr-2018 to 31-03-2019 then the fiscal year for this period is 2019 and so on.,

like wise your fiscal year work here. As per screenshot the year filter is showing correct years in your table.