Need help with Pivot and filter

[ndani]

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.

[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 · Accepted solution]

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

[fmvizcaino · Accepted solution]

Hi @ndani ,

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

Best,

Fernando Vizcaino

[Luke_C · Accepted solution]

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:

1. Parse out a date based on the input data (first of the month for each month/year data point)
2. 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. 
3. 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.
4. I use a dynamic rename to replace the record ID with the month names. 

[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.

[fmvizcaino]

Hi @ndani ,

Only for reference, newer Alteryx versions allows you to open workflows from higher versions, also you can open the workflow as a txt file and edit the workflow's version (Workflow is a xml file)

Best,

Fernando Vizcaino