Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-field Calculations, Multi-row Calculation

Joy1
8 - Asteroid

Hi, 

I am trying to do operations on hundreds of fields. Here is a simplified example of what I am trying to achieve:

 Jan 2018 - Company A RevenueFeb 2018 - Company A RevenueMarch 2018 - Company A RevenueJan 2018 - Company B RevenueFeb 2018 - Company B RevenueMarch 2018 - Company C RevenueTotal For JanTotal For FebTotal For Mar
Big Accounts6895505685100124180150
Medium Accounts44178665799710996183
Small Accounts818653217291029382

 

The idea here is that:

Field 7 (Total Jan)= Field 1 + Field 4

Field 8 (Total Feb)= Field 2 + Field 5

Field 9 (Total Mar)= Field 3 + Field 6

 

The Equation itself is a bit more complex. The obvious way to do it is to create three different equations for the three fields. Except in my case it will be 100s of equations. On excel I could just drag the equation and get all the rest.

 

Ideally, we can do an iterative equation as follows:

 

Field x = Field x-6  + Field x-3

 

The problem is that the multi-field formula only has current field with no option manipulate the index otherwise

Another option is to transpose the matrix and try to apply the multi-row formula equations. However, I failed to be able to achieve this. 

 

Any help is much appreciated. I am fairly new to Alterix so bare with me please!!

10 REPLIES 10
BrandonB
Alteryx
Alteryx

I am thinking that you can use a transpose tools to consolidate all of these columns into a name and value column. Then, you could use a text to column tool on the Name column to isolate the name of the month. Then it could be as easy as using a summarize tool to consolidate grouping by the month name.

 

Maybe give this approach a shot and let us know if you get stuck!

Joy1
8 - Asteroid

But in reality the equation is more complex:

 

It has "-" signs and may include multiplication... I just simplified it in the post to make it easier to understand

 

BrandonB
Alteryx
Alteryx

You will likely need to leverage a dynamic select to isolate the columns that need common transformation/aggregations in different streams in the workflow. Then the aggregated data can be joined back together and appropriate transformations applied. 

 

I know that this is a bit general in theory, but if you provide an example of an equation with the sample data that you provided, someone on community would be happy to mock up a workflow. 

afv2688
16 - Nebula
16 - Nebula

Hello @Joy1 ,

 

This gives you an idea of how to do it:

 

Sin título.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Joy1
8 - Asteroid

Hey,

 

The issue with your solution is that it doesn't give flexibility for equation itself. Here you are assuming it's a sum but in reality it's something like revenue from A - Revenue from B + Revenue from C * a constant etc...

 

I am looking for a more generic solutions where you can use Field [x]= (Field [x-5]+Field[x+2]-Field[x+1])* Field[x+10]

 

something more generic for field manipulation and calculations etc.

 

Thanks a lot for the support ! I really appreciate it. I am still new to Alteryx and I really appreciate you help.

danilang
19 - Altair
19 - Altair

Hi @Joy1 

 

Are you always applying the same formula to all the calculated columns in the rows?  i.e. Field [x]= (Field [x-5]+Field[x+2]-Field[x+1])* Field[x+10]

 

Dan

 

 

Joy1
8 - Asteroid

Yes. Think of it this way: If it was an excel file, I would be able to drag the equation on all new adjacent columns (which would be way easier)

 

Instead here, I would have to write the same equation (but with field index+1 for each subsequent column) repeatedly.

ChrisTX
15 - Aurora

Take a look at the CReW Dynamic Formula macro:

   https://community.alteryx.com/t5/Engine-Works-Blog/For-the-Love-of-Formulas/ba-p/20089

 

Although not directly related to your question, the post below might give you some ideas.  It uses Transpose, Crosstab, Dynamic Rename, and Dynamic Select in creative ways.

 

Fill values down multiple columns dynamically

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Developing-a-Macro-for-filling-down-va...

 

 

Chris

danilang
19 - Altair
19 - Altair

Hi @Joy1 

 

Here's 2 separate ways you could do this.

 

main.png

The top container is an extension of @afv2688's multi-row method using a more complex formula and looking back 10 rows.  

 

The bottom container uses a batch macro to process each month's worth of columns separately.

 

macro.png

 

This macro dynamically selects the all the columns for the current month and then renames the columns sequentially so the Formula Tool in the green box can always apply the same formula, [5] = ([2]+[3])*[4].  The totals that come out of each iteration are unioned and passed back to the main workflow, where this pair of columns is crosstabbed and joined to the original data.  

 

Edit:  Files versions adjusted for 2019.1. I included the document that shows how to change the version number on alteryx worklfows/macros.  

 

Dan

Labels