Fiscal Calendar

[Ankit1988]

Hi.

I have a dataset have Date Field from this date field i have to create master calendar script. Now i have to add in this master calendar script Fiscal year, Fiscal Month, fiscal Month Number. 

can any one suggest how to add this 

thanks

[Hania]

hello , 

I have created Fiscal year Master calendar on my sample data ,

you can try bellow script on your data.

/* ---------------------------------------------------------------------------*/
SET vFiscalYearStartMonth = 4;

min_max:
Load

Min(Delivery_Date) as minDate,
Max(Delivery_Date) as maxDate

Resident SALESDETAILS;

Let vMinDate = Peek('minDate',0,'min_max');

Let vMaxDate = Peek('maxDate',0,'min_max');

drop table min_max;

Fiscal_Master_Calendar:

/* ---------------------------------------------------------------------------*/
// Create Fiscal Quarter & Month Name

Load
*,
dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) as FiscalQuarter,
Dual(text(MonthName(Delivery_Date)), FiscalMonth) as FiscalMonthName,

;

/* ---------------------------------------------------------------------------*/
// Create Fiscal Month & Fiscal Year

Load

*,
mod(Month(Delivery_Date) - $(vFiscalYearStartMonth), 12) + 1 as FiscalMonth,
YearName(Delivery_Date, 0, $(vFiscalYearStartMonth)) as FiscalYear,

;

/* ---------------------------------------------------------------------------*/
// Create Fiscal Calendar Records

Load

Date($(vMinDate) + IterNo() - 1) as Delivery_Date

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

as per your needed requirement you can create Month , Year and so on. 

Hope this helps you!

[vikasmahajan]

Hi,

Please refer this link

https://community.qlik.com/t5/Member-Articles/SIMPLE-MASTER-CALENDER/ta-p/1484974

Vikas

[Prem0212]

You can use the below script  for the Master calendar

Year("Ship Date") as Year,
QuarterName("Ship Date") as Quarter,
Month("Ship Date") as Month,
Week("Ship Date") as WeekNo,
WeekDay("Ship Date") as WeekDay,
Day("Ship Date") as Day,
DayNumberOfYear("Ship Date") as DayNoYear,