Qlik Community

QlikView Documents

Documents for QlikView related information.

Fiscal Calendar with Non-Standard Days (Not 1-31)

MVP & Luminary
MVP & Luminary

Fiscal Calendar with Non-Standard Days (Not 1-31)

Hi All,

Hope the below script helps for those who are looking for a Fiscal Calendar for non standard dates.  For example the month starts from 26 th of previous month to 25th of current month.

Jan-2015: 26-Dec-2014 to 25-Jan-2015

Feb-2015: 26-Jan-2015 to 25-Feb-2015

Mar-2015: 26-Feb-2015 to 25-Mar-2015

'

'

'

'

'

For this type of Fiscal Calendar you may use below script.

LET vStartDate = MakeDate(2015, 1, 1);

LET vEndDate = MakeDate(2016, 12, 31);

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-25,1)) as FiscalDate,

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

LOAD

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

AutoGenerate $(vNumberOfDays);

Regards,

Jagan.

Comments
vikasmahajan
Esteemed Contributor

Thanks for sharing

what will be scenario if    29 - feb16  with  ??-Feb15

0 Likes
MVP & Luminary
MVP & Luminary

It will work as expect try and check.

Partner
Partner

Nice post.

This is much less complex than the calendar we used before when I had to use a broadcast calendar.

The broadcast calendar is similar to a fiscal calendar but runs Monday through Sunday so December 2015 ran Monday December 7th through Sunday January 3rd. 

0 Likes
Karim_Khan
Contributor III

But Jagan,

  

    My data i staring from 1950 to 2049 and client doesn't know last 50 year financial year starting date.he only knows the month which is basically starting form every 5th,6th or 7th of every April

0 Likes
Partner
Partner

Thanks for nice post.

0 Likes
Kevincool
Contributor III

Thanks!

0 Likes
falgunip
New Contributor III

Thanks for sharing knowledge .

0 Likes
ajsjoshua
Valued Contributor

Hi Jagan,

Great post!

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

0 Likes
Partner
Partner

Hi Jagan,

Nice Post...

I need help as below..

My date start with 17 July 2017 to 16 Aug 2017

                                       17 Aug 2017 to 16 Sep 2017......... Oct..Nov..Dec...Jan.....

                                       17 Jun 2018 to 16 July 2018 -- completes by this month.

please help to create the calendar as per this requirement.

Thanks and Regards,

Kalyan

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2016-01-28 02:20 AM
Updated by: