1 Reply Latest reply: Jan 19, 2018 11:12 AM by George Barrett RSS

    Fiscal YTD + Prior Month Flag

    George Barrett

      I am working on a project that requires me to create a flag that is marked 'Y' for the fiscal year to date and the month prior to this period. The fiscal year starts in May so the month prior will always be April. For instance, right now it is January 2018, so I need to capture everything from April 2017 to January 2018. I am currently using this for the Fiscal YTD flag: IF(YEARTODATE(Date, 0,5)=-1,'Y','N') AS YTD_Flag_Fiscal. I am having a hard time trying to modify this to include the prior month or create a new flag that only captures April of the prior period as 'Y'. Anyone have any ideas on how I can accomplish this? Thanks in advance.

        • Re: Fiscal YTD + Prior Month Flag
          George Barrett

          I figured it out shortly after posting. For anyone interested, I loaded all of the dates that are prior to the fiscal year period and assigned the flag. Then I loaded them again but only using the Max value. I joined this back to my original table so only April of the prior period is marked 'Y'.

           

          PriorMonth:

          LOAD

          Date,

              'Y' AS PriorMonthFlag

          Resident Vw_Dim_Date

          WHERE YTD_Flag_Fiscal = 'N' AND Date < Today()

          ;

           

           

          LEFT JOIN(Vw_Dim_Date)

          LOAD

          PriorMonthFlag,

          MAX(Date) AS Date

          Resident PriorMonth

          GROUP BY PriorMonthFlag;

           

           

          Drop Table PriorMonth;