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

Writing multiple formulas for each quarter

anamik72
8 - Asteroid

Hello, I have simple formulas to write for each quarter for 3 years - so 12 formulas (12 new columns). 

And then need to do the same for 7 different line items. Thus 84 formulas (and new columns). 

Is there a way to do it more intelligently?

 

See below where I multiplied "1GR" rate times "0UC" volume for each quarter.

Similarly I need to do the same for "2DF" rate times "0UC" for each quarter and so on. 

Can you help automate this instead of creating 84 manual formulas for 84 new columns? Thanks

 

anamik72_0-1588605505078.png

 

6 REPLIES 6
grossal
15 - Aurora
15 - Aurora

Hi @anamik72,

 

are you able to provide a data sample and a desired output? 

 

I think the solution will involve the usage of Transpose and Cross-Tab. These help to be more dynamic in these kind of scenarios. 

 

 

Best

Alex

Maskell_Rascal
13 - Pulsar

Hey @anamik72 

 

One solution would be to use a couple Multi-Field Formula tools to write all your formulas, and then the Dynamic Rename tool to get the correct Field Names. Not sure if this is exactly what you had in mind, but could be a possible solution for you. 

Maskell_Rascal_1-1588608040618.png

 

Maskell_Rascal_0-1588608013160.png

 

Let me know if this works for you.

 

Thanks!

Phil

 

anamik72
8 - Asteroid

Please find attached sample data. Thank you so much for your help.

Morgan_Thomas
8 - Asteroid

I had a similar thought to use transpose/cross tab and I've attached an example of what I came up with. I'm not sure if this is how your data is set up, so if you'd like to provide an example of that it would be helpful! but hope this helps: 

 

 

Morgan_Thomas
8 - Asteroid

I've attached what I've come up with. Start by transposing both input tables, grouping by Rank and Rank Name. Then I joined the data based on Rank, Rank Name, and a dynamic Year column. Then multiply and dynamically generate a name (i guessed the naming convention - hoping its right) then cross tab back out. The final select is where i reordered the columns to be grouped together - when you introduce more years you may need to reorder those in there. Im not sure if there is a way to dynamically do that. 

 

Hope this helps 🙂

grossal
15 - Aurora
15 - Aurora

I think I got you 😃

 

grossal_1-1588612974545.png

Let me try to explain what's going on:

- I transpose all data columns into rows

- Select to make sure the Values are double

- Formula to identify the Rate rows and the Volume rows

- Filter to split Rates from Volumes

- I Count up the Rates to get the original Rate order later on (Multi-Row-Formula)

- I extract the RateType (2 letters) and add them behind the ID (Formula)

- Join to bring everything back together

- Formula to multiply rates and create column names

- Cross Tab to bring everything into shape

- Dynamic Rename to remove "order identifiers" from the column names

 

 

Workflow attached. Let me know what you think.

 

 

Best

Alex

Labels