Hi - I am trying to create a column that returns the prior year (only Actuals). How would I do this? Thanks! MORCPCProductPeriodActualForecastPlanInterest CONTotal11/1/2018-18885.94-14904.9-14904.9Interest CONTotal11/1/2017-20502.23-15654.94-15654.9Interest CONTotal11/1/2019-19814.8-15504.84-15504.8Interest MMCMastercard2/1/2019-17704.99-13722.56-13722.6Interest MMCMastercard2/1/2017-19350.74-15257.76-15257.8Interest MMCMastercard2/1/2018-18894.6200Interest TECEntry Cash5/1/2019-19850.6100Interest TECEntry Cash5/1/2018-19365.5800

Prior Year Column

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

Date Time

Preparation

Accepted answers

cgoodman

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

PriorYearActuals.yxmd

Qiu

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

I made something for this test data. please check.

1120-jenner85-R1.yxmd

All comments

cgoodman

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.

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

PriorYearActuals.yxmd

Qiu

@jenner85
Hope this is what you need.

1120-jenner85.yxmd

grazitti_sapna

Hi @jenner85,

Is this what you are looking for?

I hope it solves your issue.

prior_year.yxmd

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.

test data.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15872

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703