Convert monthly payments into yearly total fields

[jmlabelle65]

I am trying to build a workflow that will take an excel file with multiple column fields including monthly payments that go out 20 years.

I would like to group this data by more than 1 column field and then summarize by year.  Is there a formula that will look at the year in a date and then create and add new fields that add up the total payments by year for each grouping?

[marcusblackhill]

Hi @jmlabelle65 !

Will be better if you can share a example data with us but I understand being some like this

Record	01/2019	02/2019
1	10	30

That way, you maybe can use transpose to to make only one column with the monthly data and create a column to get the year, maybe by DateTimeYear if you data have the datatype like date or using right formula if is string. With that new column you can use summarize tool to group by year and sum the total in values column.

Hope that help!

[Qiu]

@jmlabelle65 
I believe Transpose Tool is what we need here.

Just forged some data to demostrate.

[jmlabelle65]

Attached is a sample of the data, I would like to group by the 1st 2 fields and then sum the total payments for each by year

[marcusblackhill · Accepted solution]

@jmlabelle65 

Ok, I think that attached example will help you

[jmlabelle65]

Can you tell me how I can open the example file, there doesn't seem to be a link.

[jmlabelle65]

Never mind I see it

[Qiu]

@jmlabelle65 
Just revised the workflow based on your input file.

Appreciate you would mark it as accept if you find it help.

[jmlabelle65]

Almost.  I have attached a file with the result I am looking for better clarity.  I can do this in excel using sumproduct  but would like to have alteryx do it for me using a workflow.

Appreciate your help and sorry if I am not being clear.

[marcusblackhill]

Not a problem @jmlabelle65 

Look if now is what you need.