New Row and New Column

[tomtingley]

Hi,

I'm trying to use the attached input to do 2 things.

1) I want to create another row beneath "Payments" called "Calculation".

In this row, I want to add together the the "Payments" and "Level Expense (F)" for every month.

It would look like this:

Forecast Type                               Jan 23                and so on

Level Expense (F)                         10561
Payments                                       0
Calculation                                    10561

2) I then also want to create additional columns on the end titled "2023" "2024"

I want to sum all the "Calculation" cells for the 2023 months together to get one figure.

E.G.

Forecast Type                              Jan 23             Feb 23             Mar 23       and so on             2023
Level Expense (F)                        10561             10561              10561                                     31683

Payments                                      0                    0                       -18851                                   -18851

Calculation                                    10561             10561              -8290                                      12832

[geraldo · Accepted solution]

@tomtingley 

Here is an example of workflow

[Yoshiro_Fujimori]

Hi @tomtingley ,

When you deal with data on Alteryx, it is easier to calculate in the same row, rather than in the same column.

So I would recommend you to transpose your data to have one row for each month for the rest of the process,

so that you can apply various functions with Formula tool for each month.

That said, if you really have to keep the current format as the final output, you can transpose the table again after all the calculation.

Here is a sample workflow as one way of doing this.

Workflow

Formula

When you deal with dates, it is easier to convert to Date-Time data type,

so that you can use a bunch of functions and SORT tool later on.

Payments = IF IsNull([Payments]) THEN 0 ELSE [Payments] ENDIF

_Calculation = [Level_Expense__F_] + [Payments]

Month = DateTimeParse([Name],"%b %Y")

Year = DateTimeFormat([Month],"%Y-12-31")