Summing Rows & Adding New Row with Calculation

[AlteryxLearner23]

Hi,

I'm trying to create something using the attached file.

As you can see, I currently have a monthly view from "Jan 23" all the way to "Sep 28". On the end I have a yearly split.

Currently, the yearly columns on the far right are populated correctly, as it is adding up the respective months data for each year.

So, 2023 is Jan23 + Feb23 + Mar23 + Apr23 etc etc

How can I follow this same format for the second row? The second row is not populating the same way for the yearly split on the end.

Additionally, I want to create a third row called "Calculation" which adds up the sum of rows 1 & 2 for every column.

It will look something like this in the end:

Forecast Type                    2023      2024      2025      2026      2027      2028
Level Expense (F)             51103    10000    15000       0            0             0
Payments                          -18852   -5000     -2000        0            0            0
Calculation                        32251     5000      13000       0            0           0

[ShankerV]

Hi @AlteryxLearner23 

One way of doing this.

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @AlteryxLearner23 

Step 1: Input

Step 2: 

Step 3:

Step 4:

Step 5:

Many thanks

Shanker V

[AlteryxLearner23]

Thanks, this works well!

Out of interest, I tried to add in two new columns into the input and I received the attached output file.

How do I remove the word 'Total' from the first two columns and just keep it in the 'Forecast Type' column?

[ShankerV]

Hi @AlteryxLearner23 

One way of doing this.

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @AlteryxLearner23 

Step 1: Input data

Step 2: 

IF [ID]="Total"
THEN null()
ELSE [ID]
ENDIF

Many thanks

Shanker V