Fisical Year Display

Tue, 15 Dec 2009 22:50:45 GMT

I would like Fiscal Year to be displayed between the ranges below. Currently this script does nothing and only displays the calendar years I already have scripted. Maybe I missing the proper syntax.


FisicalYear:
MAPPING LOAD * Inline [
invoice_date, Year
(10/31/2009 - 11/01/2010), 2010,
(10/31/2008 - 11/01/2009), 2009,
(10/31/2007 - 11/01/2008), 2008,
(10/31/2006 - 11/01/2007), 2007,
(10/31/2005 - 11/01/2006), 2006,
(10/31/2004 - 11/01/2005), 2005,
(10/31/2003 - 11/01/2004), 2004,
(10/31/2002 - 11/01/2003), 2003,
(10/31/2001 - 11/01/2002), 2002,
]

I have also tried the below solution I found on the forums.


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

This solution worked better but not all my invoice_dates are showing up. My invoice_date values from the database show up in the YYYY-MM-DD TT:TT:TT.TTT format. I would say it is was a date format conversion issue but the above code 'YYYYMMDD' does convert SOME of the date values correctly and others it does not convert and thus are not displayed. I do not understand why and can not find a pattern.

Thanks for any help on the above or other methods of doing this.

Fisical Year Display

Tue, 15 Dec 2009 23:40:51 GMT

It was a date format issue I just the below in my SQL.

CONVERT(varchar(23),invoice_date,101) AS invoice_date,


,date(yearstart(invoice_date,0,3),'YYYY') as "Fiscal Year"

The 11 value displays how many months 'in' the year you want to be.

...sometimes I enjoy answering my own questions.

Hopefully this will help someone else someday.

topic Fisical Year Display in QlikView

Fisical Year Display

Fisical Year Display