YTD v PYTD and Rolling Period vs Prior Year and Proior Period

[BretPinkerton]

Hello

I am new to Alteryx and I haven't been able to locate the solution to my problem. I am trying to write several simple formulas that classifies volume and other variables by date. The goal is to end up with three columns that flag these scenarios below.

1) YTD and Prior YTD
2) Rolling 13 Weeks verse Prior Year 13 weeks
3) Rolling 13 Weeks verse Prior 13 Weeks

I would like to be able to build these three formulas so I can reference these classifications in other formulas- I need to classify variables like Volume, Outlets, Income etc. This will be going into Tableau so that is why i am setting them up with way.

Example
If [YTD and Prior YTD ] ="YTD" then [VOLUME] elseif [YTD and Prior YTD]= "PYTD" then [VOLUME] else "" ENDIF

What I would like help on is how to write the following statements

1) IF YEAR[DATE] = YEAR[MAX DATE] then "YTD" ELSEIF DATEADD(YEAR,1,[DATE]) <= [MAXDATE]
and DATEDIFF('YEAR',[DATE],[MAX DATE])=1 THEN PYTD ELSE "" ENDIF

2) Rolling 13 Weeks from [MAXDATE] and then same Period from Previous Year

3) Rolling 13 Weeks from [MAXDATE] and then the 13 weeks prior the the current 13 week period.

I have attached an excel file if that helps with the conversation.

I know this is a lot to ask so that you for your help in advance.

Bret

[AbhilashR · Accepted solution]

Hi @BretPinkerton, reading your post I think you are looking to identify rows in your data as YTD/PYTD, etc. to be used as a dimension in Tableau, is that correct? If yes, attached is a solution that gets you close to what you are looking for. I made the assumption that you are looking for a rolling 13 weeks window (which is 91 days) for a recent week.

Let us know if this isn't what you are looking for.

[BretPinkerton]

Thank you very much.  This worked well. 

Bret