2 Replies Latest reply: Feb 10, 2010 4:27 PM by Jake Bingen RSS

    YTD Fiscal Year

      I am trying to get Fiscal Year> YTD to work. When I attempt to apply the same logic I used to get YTD in Calendar Year it keeps returning calendar year YTD values.

      What do I need to change this? Below is my code for calculating fiscal year. I have attempted to calculate my own YTD equations with in the code, but was not successful.

      'master_date' is a value I share between both databases I am combining.


      [Fiscal Calendar]:
      LOAD date(date#(20011101,'YYYYMMDD')+recno(),'MM/DD/YY') AS "master_date"
      AUTOGENERATE today()-date#(20011101,'YYYYMMDD')
      LEFT JOIN ([Fiscal Calendar])
      date(monthstart(master_date),'MMM YY') AS "MonthFisical",
      date(yearstart(master_date,1,11),'YYYY') AS "YearFiscal",
      Year2Date(master_date) * -1 AS YTD_Flag_Fiscal, //attempt one
      Year2Date(master_date,-1, 1, $(vToday))*-1 AS LY_YTD_Flag_Fiscal, //attempt two
      YearToDate(master_date, -1, Year(Today(1))) AS Year_YTD_Fiscal //attempt three
      RESIDENT [Fiscal Calendar]

      Thanks for any help on this matter.