community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Calculating sum of multiple columns

Meteoroid

I currently run into the following problem:

 

Type of file: detailed income statement for one year for entities being subsidiaries of a Holding entity.

Data file: 220 columns (one column for each entitiy per month of the year, plus 1 column for the name of the income statement line item and 2 columns for the line item ID key / account number). 150 rows representing the various income statement line items.

QuestionI would like to calculate the total amount per line item for each month (e.g. I need to sum 17 entities columns for each month).

Problem: Using the 'formula' tool takes too much time, since I would need to make a sum-formula 12 times (each month of the year) and for each sum-formula I would have to select the individual 17 columns one-by-one.

 

What is the fastest way, using the minimum amount of tools possible???

Nebula
Nebula
You can also use a multi-field formula to apply the same formula to multiple fields.

As @jdunkerley79 said - best is to provide some sample data, and we can tear into it.
Cheers
Sean
Alteryx
Alteryx

Hi @Koen_Stam

 

I would look to transpose the monthly fields you referenced so you have a name (Months) and value(Data) field. You can then use the key fields option to group by the name of the income statement, line item etc. This would be the level of specificity you want the summation to be in.  

 

You would then use a summarize tool to group by line item and month and sum the line item fields. 

 

I have attached a rough example using pet store data with monthly sales. Hopefully this can explain the process visually.

 

best,

 

Jordan Barker

Solutions Consultant 

 

Could you provide a sample of the data?

 

I would guess from what you describe you would need to transpose the data so the months were used as a row field. Then the problem would be a lot simpler to solve.

Labels