Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ajsjoshua
Specialist
Specialist

Dec 16 2016 as start date for a year

Dear all,

I am using master calendar i want Dec 16 2016 to Jan 14 2017 as month Jan.

Jan 15 2017 to Feb 14 2017 as month feb.

How to achieve this?

7 Replies
marcus_sommer

Take a look here: Fiscal Year

- Marcus

ajsjoshua
Specialist
Specialist
Author

Hi marcus,

Thanks for ur reply.

Set vFM = 12 ;                                                          // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year
         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

          *;

Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

          *;

Load Year(Date)                              as Year,           // Your standard master calendar

         Month(Date)                            as Month,

As i am declaring start month as December my date also should start from Dec 16 how to declare?

Jan-(Dec16 2016 to Jan 14 2017)

Feb(Jan 15 2017 to Feb 12 2017)

marcus_sommer

I think this will be helpful: Fiscal Calendar with Non-Standard Days (Not 1-31).

- Marcus

ajsjoshua
Specialist
Specialist
Author

Dear Marcus,

Its Working fine for all months but for jan it brings only dates from Jan its not showing the previous year dates.

LET vStartDate = MakeDate(2016, 12, 16);

    LET vEndDate = MakeDate(2017, 7, 7);

    LET vNumberOfDays = vEndDate - vStartDate + 1;

    

    FiscalCalendar:

    LOAD

    *,

    Month(FiscalDate) AS Month,

    Year(FiscalDate) AS Year,

    'Q' & Ceil(Month(FiscalDate)/3) AS Quarter,

    MonthName(FiscalDate) AS MonthYear,

    'Q' & Ceil(Month(FiscalDate)/3) & '-' & Year(FiscalDate) AS QuarterYear;

    LOAD Date,

    Date(monthstart(Date-15,1)) as FiscalDate,

    Day(Date(Date-15)) AS DayNumberInMonth;

    LOAD

    Date(makedate(2017)+recno()-1) as Date

    AutoGenerate $(vNumberOfDays);  !

kkkumar82
Specialist III
Specialist III

Hi Solomon,

Is this assumption for all years , I mean 16 to 15 for Dec, 15 to 14 for Feb like that, if this is changing for every financial year better create an excel file which should clearly say when your fiscal month is starting and ending for each month in each year then use that Excel in your master calendar preparation.

Hope I am clear.

Regards,

Kiran Kumar

marcus_sommer

These records aren't created because you start with makedate(2017) but the fiscal January 2017 would need to start with num($(vStartDate)).

- Marcus

ajsjoshua
Specialist
Specialist
Author

Hi kiran,

yes for all years.