Need help with Pivot and filter

Hi, I am new to Alteryx and still learning the transform functions. I think I need to use the Cross-Tab for this but could use some help.

I have monthly data broken by year in different rows for the same item. I need to put it all together in a single row/item and at the same time filter for last n months. For example in the the screenshot below, I have filtered data from Apr'20 through Mar'21.

I have attached the sample excel file as well. I'd appreciate any guidance.

Pivot_Example.xlsx

Accepted answers

markcurry

Hi @ndani I've attached a workflow which will hopefully do the trick for you.

It calculates the current year and previous month, so at the moment, will give you the 12 months up until March 2021.

Hope that helps,

Mark

Pivot and Filter.yxmd

fmvizcaino

Hi @ndani ,

Here are 2 approaches, the easier and the complete one.

Best,

Fernando Vizcaino

2021-04-05 Need help with Pivot and filter.yxmd

Luke_C

Hi @ndani

Like @Emil_Kos mentioned, you can generally accomplish this with the data transformation tools (summarize, transpose, crosstab). The date filtering and ordering of the output made this a little trickier.

To accomplish this I did the following:

Parse out a date based on the input data (first of the month for each month/year data point)
Dynamically filter on the last 12 months data using the datetimenow and datetimeadd formulas. (The formula uses -13 to exclude the current month). This is where you can update the 'n' number of months.
Based on this filter I sample the first 12 records to get the order of the months for the output column, and add a record ID to order them in the output.
I use a dynamic rename to replace the record ID with the month names.

New Workflow1.yxmd

All comments

Emil_Kos

Hi @ndani,

i dont have access to Alteryx but try using sumarize tool and group by key and sum by month names.

you should be able to figure it on your own in no time!

markcurry

Hi @ndani I've attached a workflow which will hopefully do the trick for you.

It calculates the current year and previous month, so at the moment, will give you the 12 months up until March 2021.

Hope that helps,

Mark

Pivot and Filter.yxmd

fmvizcaino

Hi @ndani ,

Here are 2 approaches, the easier and the complete one.

Best,

Fernando Vizcaino

2021-04-05 Need help with Pivot and filter.yxmd

Luke_C

Hi @ndani

To accomplish this I did the following:

Parse out a date based on the input data (first of the month for each month/year data point)
Dynamically filter on the last 12 months data using the datetimenow and datetimeadd formulas. (The formula uses -13 to exclude the current month). This is where you can update the 'n' number of months.
Based on this filter I sample the first 12 records to get the order of the months for the output column, and add a record ID to order them in the output.
I use a dynamic rename to replace the record ID with the month names.

New Workflow1.yxmd

ndani

Hi @markcurry , This worked exactly as I wanted. I had to make one change. The "YEAR" and "MONTH" functions to calculate the "Current Year" and "Current Month" gave me an error. I had to change it to DateTimeYear(DateTimeNow()) and DateTimeMonth(DateTimeNow()) and it worked great. I am using Alteryx 2020.3.4

Thanks so much for a quick reply.

ndani

Thank @Emil_Kos : Thanks for the reply. As others mentioned, filtering requirement made it little tricky and simly summarize would not fully meet the need. But the solution that @markcurry provided works perfectly.

ndani

Hi @fmvizcaino Thanks for the prompt reply. Unfortunately, I am not able to open your flow because of the version conflict. That said, looks like it does provide the expected outcome. Thanks so much.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703