Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Create a Fiscal Calendar with non-standard dates

Good morning,

I've found a few discussions and blog posts which state how to create a Fiscal Calendar where they run from the beginning of the month to the end of the month, but I haven't yet found anything that can help me to solve my requirement, so I'm hoping someone can help me out.

Our Fiscal Year runs from January 1st to December 31st, but each Fiscal Period ends on the last Friday of each month, with the exception of Period 12, which ends on December 31st.

For 2015, this looks like the following (DD/MM):

Period 1: 01/01- 30/01

Period 2: 31/01 - 27/02

Period 3: 28/02 - 27/03

Period 4: 28/03 - 24/04

Period 5: 25/04 - 29/05

Period 6: 30/05 - 26/06

Period 7: 27/06 - 31/07

Period 8: 01/08 - 28/08

Period 9: 29/08 - 25/09

Period 10: 26/09 - 30/10

Period 11: 31/10 - 27/11

Period 12: 28/11 - 31/12

Can anyone tell me how I can write a script that will automatically create the Fiscal periods for each year automatically?

Tags (2)
1 Solution

Accepted Solutions
Employee
Employee

Re: Create a Fiscal Calendar with non-standard dates

One way to do it is the following:

Load Num(
If(Date>FiscalPeriodEnd, CalendarMonth+1,
If(Date<FiscalPeriodStart, CalendarMonth-1,
CalendarMonth))) as FiscalPeriod,
*;
Load
If(Month(Date)=1, MonthStart(Date),Date(MonthStart(Date)-WeekDayMonthStart)) as FiscalPeriodStart,
If(Month(Date)=12, Date(Floor(MonthEnd(Date))),Date(Floor(MonthEnd(Date)-WeekDayMonthEnd))) as FiscalPeriodEnd,
*;
Load
Dual(WeekDay(MonthEnd(Date)), Mod(3+WeekDay(MonthEnd(Date)),7)) as WeekDayMonthEnd,
Dual(WeekDay(MonthStart(Date)), Mod(2+WeekDay(MonthStart(Date)),7)) as WeekDayMonthStart,
WeekDay(Date) as WeekDay,
Month(Date) as CalendarMonth,
Year(Date) as CalendarYear,
*;
Load Date(RecNo()-1+MakeDate(2000)) as Date Autogenerate 10000;

HIC

4 Replies
Employee
Employee

Re: Create a Fiscal Calendar with non-standard dates

One way to do it is the following:

Load Num(
If(Date>FiscalPeriodEnd, CalendarMonth+1,
If(Date<FiscalPeriodStart, CalendarMonth-1,
CalendarMonth))) as FiscalPeriod,
*;
Load
If(Month(Date)=1, MonthStart(Date),Date(MonthStart(Date)-WeekDayMonthStart)) as FiscalPeriodStart,
If(Month(Date)=12, Date(Floor(MonthEnd(Date))),Date(Floor(MonthEnd(Date)-WeekDayMonthEnd))) as FiscalPeriodEnd,
*;
Load
Dual(WeekDay(MonthEnd(Date)), Mod(3+WeekDay(MonthEnd(Date)),7)) as WeekDayMonthEnd,
Dual(WeekDay(MonthStart(Date)), Mod(2+WeekDay(MonthStart(Date)),7)) as WeekDayMonthStart,
WeekDay(Date) as WeekDay,
Month(Date) as CalendarMonth,
Year(Date) as CalendarYear,
*;
Load Date(RecNo()-1+MakeDate(2000)) as Date Autogenerate 10000;

HIC

Not applicable

Re: Create a Fiscal Calendar with non-standard dates

Thanks so much Henric! That answer is perfect - it does exactly what I'm after.

Not applicable

Re: Create a Fiscal Calendar with non-standard dates

Hi hicjamie.maguire‌ ,

I am looking similar type of fiscal calendar. every period starts on monday and ends on sunday .

Screenshot.jpg

Can anyone tell me how I can write a script that will automatically create the Fiscal periods for each year automatically?

Thanks

Venkat hic

Not applicable

Re: Create a Fiscal Calendar with non-standard dates

hic

Condition: Each period starts on Monday which is nearer to 1st of that month.

Thanks

Venkat

Community Browser