Prior Year Column

[jenner85]

Hi - 

I am trying to create a column that returns the prior year (only Actuals). How would I do this? Thanks!

MOR	CPC	Product	Period	Actual	Forecast	Plan
Interest	CON	Total	11/1/2018	-18885.94	-14904.9	-14904.9
Interest	CON	Total	11/1/2017	-20502.23	-15654.94	-15654.9
Interest	CON	Total	11/1/2019	-19814.8	-15504.84	-15504.8
Interest	MMC	Mastercard	2/1/2019	-17704.99	-13722.56	-13722.6
Interest	MMC	Mastercard	2/1/2017	-19350.74	-15257.76	-15257.8
Interest	MMC	Mastercard	2/1/2018	-18894.62	0	0
Interest	TEC	Entry Cash	5/1/2019	-19850.61	0	0
Interest	TEC	Entry Cash	5/1/2018	-19365.58	0	0

[cgoodman3 · Accepted solution]

Without an example of the desired output, this is my guess at what you are trying to get as your output.

If that's the output you desire then there are a few things to do with your data.

1) You need to make the date column into a date format, as the order of dates is not consistent. Alteryx expects dates in yyyy-MM-dd format which you can create a new field in this format using the date time tool.

2) With the data sorted you can use a multi-row formula tool to bring through a value from the proceeding record.

The trick here is to group on MOR, CPC,Product

[Qiu]

@jenner85 
Hope this is what you need.

[grazitti_sapna]

Hi @jenner85,

Is this what you are looking for?

I hope it solves your issue.

[jenner85]

@Qiu  @grazitti_sapna  I need to return the prior year value that is under the "Actual" column. 

[jenner85]

@cgoodman3   I am having a hard time getting this to work for my data - it keeps returning the prior month value and not the prior year. This is the output I am getting, not sure where I am going wrong. 

[Qiu]

@jenner85 
Sorry I misunderstood and please check this revised one.

[Qiu · Accepted solution]

@jenner85 
Your test data is very much different with previous sample data, I believe.

I made something for this test data. please check.