Finding Average based on column name criteria

Question

Hi I have a dataset where prices are entered on columns with their exact dates but I intend to transform the data into a table that finds the average of the Months based on the column dates.

I have provided an example to illustrate my current database.

An example of my current data is :

I would want to create a table that output would look like this:

I intend to create a table that would be able to update with new data every month so could anyone guide me in the right direction?

Thank you!

Alteryx Help.yxmd

estherb47 · Accepted Answer

Hi @csprch

You can transpose the data, and then use a formula tool to create a new header based on the date. This function will pull just the month and year out: Substring([Name],FindString([name], " ")+1,length([Name])-FindString([name], " "))

Then a crosstab with Average builds back the desired table.

Let me know if this helps. You can add a dynamic rename to make the column headers exactly as desired, at the end. Rename by a formula on the 2 Month columns. Regex_Replace([_CurrentField_]),"(.*)_(.*)_(.*)","$2 $3 $1")

Cheers!

Esther