community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Create a column for Current Year and one for Previous Year values

Highlighted
Asteroid

Right now I have the Current Year column done by using the Summarize tool; [Year] Max.

 

How can I get the Prior Year values by changing the formula to pull [Year] Max minus 1?  Would I do this using the formula tool?

 

The objective is to make it so that it will automatically update the current year and prior year when 2019 hits.

Alteryx Certified Partner
Alteryx Certified Partner

Hi @MPCNA

Maybe calculating the LY values with a formula and joining values on that field would work.

If you post a more detailed use case, I can help you better.

Best,

_AG_

Pulsar
Pulsar

Hey @MPCNA!

 

f you just need to capture this year and last year, you could simply add a Formula tool to create one field for this year with this expression:

DateTimeFormat(DateTimeToday(),"%Y")

and a field for last year with this expression:

tonumber(DateTimeFormat(DateTimeToday(),"%Y"))-1

Does this accomplish what you're looking for?

 

years.PNG

Asteroid

In the end, the objective is to compare this years YTD (Feb) results to last years YTD (Feb) results, to calculate the growth. I will be updating the data every month to include the final results of the previous month. 

 

Additionally, the less complex table will show the full year 2018 vs 2017 results and the respective growth.

 

Let me know if this helps, or you need more detail.

Alteryx Certified Partner
Alteryx Certified Partner

How about splitting the data by year (one stream corresponding to TY the other to LY) and then joining them together by month (and maybe other field?

Do you have one record per month? Maybe you require to summarize the info first.

Asteroid

Maybe this can help show where I am at. 

Asteroid

I'm not totally sure that would be the solution. Based on the results image, it looks like the dates are being pulled. I want to have an entire column showing only the 2018 results and another entire column only showing the 2017 results. I want to have the columns pull back and show the dollar amounts of that year from the dataset.

Pulsar
Pulsar

Could you just create some dummy data to show us what your data looks like before and what you want it to look like after? Then we can best help show you how to get from point A to piont B.

Alteryx Certified Partner
Alteryx Certified Partner

Hi,

 

This workflow using sample superstore should work.

It is dynamic so if the data were to update with 2019 data then it would pull this through as the current year and 2018 as the previous year.

 

Ben

Alteryx Certified Partner
Alteryx Certified Partner

Duplicate post

Labels