Fiscal YTD + Prior Month Flag

[gbarrett]

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.

[gbarrett · Accepted solution]

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;