1 Reply Latest reply: Dec 15, 2009 6:40 PM by Jake Bingen RSS

    Fisical Year Display

      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.


      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])
      ,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

          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.