Writing multiple formulas for each quarter

[anamik72]

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

[grossal]

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]

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. 

Let me know if this works for you.

Thanks!

Phil

[anamik72]

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

[Morgan_Thomas]

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 · Accepted solution]

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]

I think I got you 😃

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