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 Revenue | Feb 2018 - Company A Revenue | March 2018 - Company A Revenue | Jan 2018 - Company B Revenue | Feb 2018 - Company B Revenue | March 2018 - Company C Revenue | Total For Jan | Total For Feb | Total For Mar | |
Big Accounts | 68 | 95 | 50 | 56 | 85 | 100 | 124 | 180 | 150 |
Medium Accounts | 44 | 17 | 86 | 65 | 79 | 97 | 109 | 96 | 183 |
Small Accounts | 81 | 86 | 53 | 21 | 7 | 29 | 102 | 93 | 82 |
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!!
Solved! Go to Solution.
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!
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
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.
Hello @Joy1 ,
This gives you an idea of how to do it:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
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.
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
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.
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
Chris
Hi @Joy1
Here's 2 separate ways you could do this.
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.
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