topic Re: Customized Fiscal year.. in QlikView

Customized Fiscal year..

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am new to Qlik. And never implemented Customized fiscal year.

Can some one help with the script for below

Thanks in advance

Re: Customized Fiscal year..

marcus_sommer — Mon, 02 May 2016 13:58:11 GMT

Have a look on these examples:

Fiscal Year

Fiscal Calendar with Non-Standard Days (Not 1-31)

- Marcus

Re: Customized Fiscal year..

Mon, 02 May 2016 14:04:46 GMT

Thanks for fast response. I will work on this. Looks like it works for me.. Thanks

Re: Customized Fiscal year..

Mon, 02 May 2016 19:20:39 GMT

Hi marcus_sommer‌

Can you make changes to the below code.

I have date field called "Orderdate"

Start date: 2016-jan-04

End date: 2017-Jan-01.

LET vStartDate = MakeDate(2015, 1, 1);
LET vEndDate = MakeDate(2016, 12, 31);
LET vNumberOfDays = vEndDate - vStartDate + 1;
FiscalCalendar:
LOAD
*,
Month(FiscalDate) AS Month,
Year(FiscalDate) AS Year,
'Q' & Ceil(Month(FiscalDate)/3) AS Quarter,
MonthName(FiscalDate) AS MonthYear,
'Q' & Ceil(Month(FiscalDate)/3) & '-' & Year(FiscalDate) AS QuarterYear;
LOAD Date,
Date(monthstart(Date-25,1)) as FiscalDate,
Day(Date(Date-25)) AS DayNumberInMonth;
LOAD
Date(makedate(2015)+recno()-1) as Date
AutoGenerate $(vNumberOfDays);

What if my date have before and after this start and end dates..?

Thanks

Re: Customized Fiscal year..

marcus_sommer — Tue, 03 May 2016 05:03:09 GMT

There are several ways possible. One is to include a longer period within the calendar and to delete the excess dates afterwards with a where-clause and another (I think more commenly used) is to create the calendar fom the min to max dates within the fact-table data. Here is an example where the min/max dates from the order-table will be used (filling the variable with a peek-function) to determine the scope of the calendar:

Better Calendar Scripts | Qlikview Cookbook

- Marcus