Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Need help with Pivot and filter

ndani
7 - Meteor

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.

 

ndani_0-1617652427778.png

 

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

 

 

8 REPLIES 8
Emil_Kos
17 - Castor
17 - Castor

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
12 - Quasar

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
17 - Castor
17 - Castor

Hi @ndani ,

 

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

fmvizcaino_1-1617656208674.png

 

 

 

Best,

Fernando Vizcaino

 

Luke_C
17 - Castor

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. 

 

Luke_C_1-1617656471555.png

 

ndani
7 - Meteor

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
7 - Meteor

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
7 - Meteor

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
17 - Castor
17 - Castor

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)

 

fmvizcaino_0-1617659006640.png

 

Best,

Fernando Vizcaino

 

Labels