Only Keep Max Date Field Name in a dynamic Data set

Question

Hi Everyone,

I have a data set that is structured as follows:

This data will be updated monthly to include a new column, which will contain the most recent month's data. I'm looking to create a workflow that will only include the most recent Sales month column. It does need to be dynamic enough to allow for the workflow to run each month and only keep the most recent month data that has been added.

The desired output is as follows:

I attached the data in this post. This data includes an input tab and also a desired output tab. I made some progress on a workflow, but I can't tell if my workflow is headed in the right direction or needs to be redone entirely. The output is close but not what I need.

Any help is greatly appreciated.

Thank you,

Paul

AlteryxDiscussion_MaxYear.yxmd

MonthOverMonth_AlteryxDiscussion.xlsx

atcodedog05 · Accepted Answer

Hi @paulwini

Here is how you can do it.

Workflow:

1. Using transpose to convert columns into row. Once converted the columns will be in order i,e last/max date column will be last row of each ids

2. Using sample tool to get only last row for ids

3. Using crosstab to convert back to table.

4. Using dynamic rename to fix the column name issues.

Hope this helps : )