Qlik Community

QlikView Documents

Documents for QlikView related information.

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

MVP
MVP

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

Thanks for sharing

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

MVP
MVP

It will work as expect try and check.

dgreenberg
Contributor III

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. 

karimkhan
Contributor II

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

yadhunandhanan
New Contributor II

Thanks for nice post.

karthikmungi3
Contributor III

Thanks!

falgunip
New Contributor III

Thanks for sharing knowledge .

ajsjoshua
Contributor III

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.

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