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 

[Aspiring_Developer]

Hi , Can anyone please help me with this ? Thanks

[qv_testing]

Let varMinDate = Num(Makedate(2023,1,1));

Let varMaxDate = Num(Makedate(Year(today()),Month(today()),Day(today())));

Datefield:

LOAD date($(varMinDate)+IterNo()-1) AS Datefield

AUTOGENERATE (1)

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

Set vFM = 4 ;                                                          // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

     Dual(Month, fMonth)              as FMonth,            // Dual fiscal month

          *;

Load Year + If(Month>=$(vFM), 1, 0)  as fYear,         // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1      as fMonth,         // Numeric fiscal month

          *;

Load Year(Datefield)                  as Year,           // Your standard master calendar

         Month(Datefield)             as Month,

Datefield

Resident Datefield;

DROP Table Datefield;

[Manojkumar_250830 · Accepted solution]

MasterCalendar:

TempDate AS EMLC_OK_DATE,       // Normal Calendar
week(TempDate) As RFS_Fiscal_Week,      // Normal calendar Week
Year(TempDate) As RFS_Fiscal_Year,        // Normal Calendar year
Month(TempDate) As RFS_Foscal_Month  // Normal Calendar Month,

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

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

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

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

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

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

(Num(MOnth(TempDate)),'10','11,'12'), 'Q3',(Num(MOnth(TempDate)),'1','2','3'), 'Q4')))) as Fiscal Quater

Resident TempCalendar
Order By TempDate ;
Drop Table TempCalendar;

[vuan]

the easiest way is using addmonth() with your offset 

Let vMinDate = Num(Makedate(2008,1,1));
Let vMaxDate = Num(Makedate(Year(today())+1,12,31));
Set vOffset = 5; 
Master_Calendar:

Load *,
MonthName($(vDatefield)) as Monthname,
QuarterName($(vDatefield)) as QuarterName,
'Q'&Ceil(Month($(vDatefield))/3) as Quarter,
Year($(vDatefield)) as Year,
Year(AddMonths($(vDatefield),$(vOffset))) as Fiscal_Year,
Month($(vDatefield)) as Month,
num(Month(AddMonths($(vDatefield),$(vOffset))),'00') as Fiscal_Month_Num,
Year(AddMonths($(vDatefield),$(vOffset)))&
num(Month(AddMonths($(vDatefield),$(vOffset ))),'00') as Fiscal_Periode,
WeekDay($(vDatefield)) as Weekday;

Load
Date($(vMinDate) + IterNo() - 1) as $(vDatefield)
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Best Regards

[Aspiring_Developer]

Hi @Manojkumar_250830  

Thanks for your response. I used the above script but in my app the data is commencing from 2017 till now but as of now it is showing garbage values like below :-

[Aspiring_Developer]

Hi @vuan  

Thanks for your response .

In my data set here are my min date (03/04/2017)  and max date is below:-

The max date is 13/03/2024 and I have a garbage value as 01/01/9999. How should I deal with this ?

Thanks

[vuan]

Hi,

you can try to limit your data to today's year.

Temp:
Load
min(EMLC_OK_DATE) as minDate,
max(EMLC_OK_DATE) as maxDate
Resident ZZ_FF_BUILD_DETAIL_DAILY where year(EMLC_OK_DATE)<= year(today());

[Manojkumar_250830]

@Aspiring_Developer : If you are facing any garbage values, make sure restrict your calendar based on Mindate. (where EMLC_OK_DATE>='01/04/2017')

Or else Please use below master calendar script.

MasterCalendar:
Load
TempDate AS LinkDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
Month(TempDate) &Chr(39)& Right(Year(TempDate),2) as MonthYear1,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,

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

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

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

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

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

(Num(MOnth(TempDate)),'10','11,'12'), 'Q3',(Num(MOnth(TempDate)),'1','2','3'), 'Q4')))) as Fiscal Quater

;

LOAD
date(mindate + IterNo()) AS TempDate,
maxdate
WHILE mindate + IterNo() <= maxdate;

LOAD
min(FieldValue('EMLC_OK_DATE', recno()))-1 as mindate,
max(FieldValue('EMLC_OK_DATE', recno())) as maxdate
AUTOGENERATE FieldValueCount('EMLC_OK_DATE');

[Aspiring_Developer]

Hi , Thanks for your response. I tried the above solution ,it kind of worked . However, i also need to show Fiscal monthyear column that should show the values like this 2017-04 when Q1 is selected , similarly for other selections :-