Qlik Community

QlikView Documents

Documents for QlikView related 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

0 Likes
tymiller
New Contributor

olivier_esmieu Glad to have helped!

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