Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Custom Fiscal Calendar

tymiller
New Contributor

Custom Fiscal Calendar

All,

I have done some searching around the Qlik Community and found plenty of helpful documentation on creating a Fiscal Calendar that starts on the first day of a month.  Adding to that, I have developed a way to start the Fiscal Calendar on a particular day of the month.  If this has already been posted (I couldn't find anything) I apologize.

Assumptions:

1.  Fiscal Year starts on the Thursday before the first Saturday in October.

2.  Fiscal Year is 12 Periods (Months), 52 Weeks (Thurs-Weds each week), 364 Days.

3.  Fiscal Months sequence is 4-4-5 (4 weeks, 4 weeks, 5 weeks, repeat sequence).

4.  Data is to be reported at the weekly grain

Script:

//Custom Fiscal Calendar starting on the Thursday prior to the first Saturday in October:

//-------------------------------------------------------------------------------------------------------------------------------

set vFM = 10; //start the fiscal calendar on first day of October

set vFSD = 5; //set the fiscal start day of October to Saturday

set vFD = 3; //set the first day of the fiscal week to Thursday

//-------------------------------------------------------------------------------------------------------------------------------

MapPeriod:

mapping load

       FiscalWeekNum,

        FiscalPeriod

FROM

[Source.xlsx]

(ooxml, embedded labels, table is FiscalMap);

MapWeekOfPeriod:

mapping load

       FiscalWeekNum,

        FiscalWeekOfPeriod

FROM

[Source.xlsx]

(ooxml, embedded labels, table is FiscalMap);

//-------------------------------------------------------------------------------------------------------------------------------

//load data in at weekly grain.

Fact:

NoConcatenate

load

       'P' & num(FiscalPeriodNum,'00') & '-' & num(FiscalWeekOfPeriod,'00')        as FiscalPeriodWeek, //concatenated field for front-end Users to select on Weekly tab

       'P' & num(FiscalPeriodNum,'00') & '-' & num(FiscalWeekOfPeriod,'00') & '-' & FiscalYearNum     as %PeriodWeekYear_KEY, //concatenated key for scripting

       *

;

load

       applymap('MapPeriod',FiscalWeekNum,null())               as FiscalPeriodNum, // map the week # (1-52) to a period (1-12)

       applymap('MapWeekOfPeriod',FiscalWeekNum,null())   as FiscalWeekOfPeriod, //map the week # (1-52) to a week of period (1-5) based on 4-4-5 calendar

       *

;

load

        ceil((Date - StartOfWeekOne +1) / 7)      as FiscalWeekNum, //Dates grouped into weeks (1-52) based on the start of the work Fiscal Year

       *

;

load

       weekstart(FiscalYearStart,0,$(vFD))       as StartOfWeekOne, //Start the work Fiscal Year on the first Thursday prior to the first Saturday of the Fiscal Year

       weekend(FiscalYearStart,0,$(FSD)-1)     as StartofFiscalYear, //check to make sure FiscalYearStart is working correctly     

        *

;

load

        (weekend(yearstart(num(date(floor(Date),'MM/DD/YYYY')),0,$(vFM)),0,$(FSD)-1) - yearstart(num(date(floor(Date),'MM/DD/YYYY')),0,$(vFM)))

                    + yearstart(num(date(floor(Date),'MM/DD/YYYY')),0,$(vFM))           as FiscalYearStart, //Start the regular Fiscal Year on the first Saturday of October

       Year(num(date(floor(Date),'MM/DD/YYYY')))                                 as Year, //Calendar Year

       month(num(date(floor(Date),'MM/DD/YYYY')))                                as Month, //Calendar Month

       num(date(floor(Date),'MM/DD/YYYY'))                                       as Date //raw field value comes in as "MM/DD/YYYY"...convert this to a number

FROM

[Source.qvd]

(qvd)

where num(date(floor(Date),'MM/DD/YYYY')) >= 41914

; //load data starting on first day of work fiscal year 2015

Please post any comments/questions.

Thanks,

Tyler

Labels (2)
Comments
MVP & Luminary
MVP & Luminary

Yes, there was already one: Fiscal Calendar with Non-Standard Days (Not 1-31) but each working solution creates new/further insights - therefore thank you for your efforts to share your knowledge and experience.

- Marcus

Not applicable

Tyler

Many, many thks for your Fisacl week formula. definitely helped

Olivier

tymiller
New Contributor

olivier_esmieu Glad to have helped!

Version history
Revision #:
1 of 1
Last update:
‎03-11-2016 12:06 PM
Updated by: