topic Calendar Object Based on Fiscal Calendar in QlikView

Calendar Object Based on Fiscal Calendar

karensmith — Mon, 22 Jun 2009 19:13:40 GMT

How do you make a calendar object based on Fiscal Calendar(April- March)?

Calendar Object Based on Fiscal Calendar

johnw — Mon, 22 Jun 2009 23:45:52 GMT

I'm not sure quite what you're asking. When I make calendars, I'm just tying dates in to other "formats" of the date. For a calendar in that sense, something like this (untested):

[Fiscal Calendar]:
LOAD date(date#(20061231,'YYYYMMDD')+recno(),'MM/DD/YY') as "Date"
AUTOGENERATE today()-date#(20061231,'YYYYMMDD')
;
LEFT JOIN ([Fiscal Calendar])
LOAD
 "Date"
,date(monthstart(Date),'MMM YY') as "Month"
,date(yearstart(Date),'YYYY') as "Calendar Year"
,date(yearstart(Date,0,4),'YYYY') as "Fiscal Year"
RESIDENT [Fiscal Calendar])
;

Calendar Object Based on Fiscal Calendar

karensmith — Wed, 24 Jun 2009 00:31:02 GMT

John,

Thanks for your assistance. This code worked great. It created calendar up to current date. One more question- How would create calendar days greater than current date.

Calendar Object Based on Fiscal Calendar

johnw — Wed, 24 Jun 2009 01:08:03 GMT


KarenSmith wrote:Thanks for your assistance. This code worked great. It created calendar up to current date. One more question- How would create calendar days greater than current date.

Just change this part of the code:

AUTOGENERATE today()-date#(20061231,'YYYYMMDD')

The AUTOGENERATE just generates a certain number of occurrences. Here, I calculate the number of occurrences such that it will generate everything between January 1, 2007 and today(). If you want a calendar through the end of the current year, for instance, you could do this:

AUTOGENERATE yearend(today())-date#(20061231,'YYYYMMDD')