topic Re: Create fiscal year calendar in Qliksense in App Development

Create fiscal year calendar in Qliksense

Aspiring_Developer — Mon, 18 Mar 2024 15:46:06 GMT

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

Re: Create fiscal year calendar in Qliksense

Aspiring_Developer — Tue, 19 Mar 2024 07:18:59 GMT

Hi , Can anyone please help me with this ? Thanks

Re: Create fiscal year calendar in Qliksense

Nagaraju_KCS — Tue, 19 Mar 2024 08:32:11 GMT

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;

Re: Create fiscal year calendar in Qliksense

Manojkumar_250830 — Tue, 19 Mar 2024 12:24:01 GMT

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;

Re: Create fiscal year calendar in Qliksense

vuan — Tue, 19 Mar 2024 13:14:32 GMT

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

Re: Create fiscal year calendar in Qliksense

Aspiring_Developer — Tue, 19 Mar 2024 13:40:59 GMT

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

Re: Create fiscal year calendar in Qliksense

Aspiring_Developer — Tue, 19 Mar 2024 13:47:53 GMT

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

Re: Create fiscal year calendar in Qliksense

vuan — Tue, 19 Mar 2024 13:54:12 GMT

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

Re: Create fiscal year calendar in Qliksense

Manojkumar_250830 — Tue, 19 Mar 2024 14:51:30 GMT

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

Re: Create fiscal year calendar in Qliksense

Aspiring_Developer — Tue, 19 Mar 2024 15:35:31 GMT

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

Re: Create fiscal year calendar in Qliksense

Manojkumar_250830 — Tue, 19 Mar 2024 17:01:59 GMT

@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

Re: Create fiscal year calendar in Qliksense

Aspiring_Developer — Tue, 19 Mar 2024 17:42:01 GMT

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

Re: Create fiscal year calendar in Qliksense

Manojkumar_250830 — Wed, 20 Mar 2024 08:56:21 GMT

@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.