Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ajsjoshua
Contributor III

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?

Tags (1)
7 Replies

Re: Dec 16 2016 as start date for a year

Take a look here: Fiscal Year

- Marcus

ajsjoshua
Contributor III

Re: Dec 16 2016 as start date for a year

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)

Re: Dec 16 2016 as start date for a year

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

- Marcus

ajsjoshua
Contributor III

Re: Dec 16 2016 as start date for a year

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
Valued Contributor III

Re: Dec 16 2016 as start date for a year

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

Re: Dec 16 2016 as start date for a year

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
Contributor III

Re: Dec 16 2016 as start date for a year

Hi kiran,

yes for all years.