topic Create a Fiscal Calendar with non-standard dates in QlikView

Create a Fiscal Calendar with non-standard dates

Tue, 13 Oct 2015 10:46:27 GMT

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?

Re: Create a Fiscal Calendar with non-standard dates

hic — Tue, 13 Oct 2015 13:10:14 GMT

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

Re: Create a Fiscal Calendar with non-standard dates

Fri, 16 Oct 2015 08:09:31 GMT

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

Re: Create a Fiscal Calendar with non-standard dates

Mon, 02 May 2016 20:14:01 GMT

Hi hic‌ jamie.maguire‌ ,

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

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

Thanks

Venkat hic‌

Re: Create a Fiscal Calendar with non-standard dates

Mon, 02 May 2016 20:29:40 GMT

hic‌

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

Thanks

Venkat