Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Formula off a Dynamic List

bebrown
5 - Atom

Hey Everyone -

 

I'm trying to create a analysis workflow where I compare vendors (A thru D in my examples) prices over the same locations (states AK - AR). Where I'm stuck is creating a single formula that will calculate the % variance from the lowest price for each vendor without having to create a unique formula for each vendor. My list of vendors is constantly changing and not a short list so I'm trying to avoid having to re-create this for each data set. Is there anyway to make a formula/macro that would dynamically add a column for each vendors % variance to the lowest price? Or In my example would I be stuck making these 4 formulas (A % vs Lowest Value, B % vs Lowest Value, etc). 

 

Here is an example below with my vendors (A - D) and what I'm trying to accomplish in bold. 

 

 Competitors     
StateABCDLowest ValueA % vs Lowest ValueB % vs Lowest ValueC % vs Lowest ValueD % vs Lowest Value
AL1001251101051000%25%10%5%
AK75708090707%0%14%29%
AR4530201515200%100%33%0%
AZ908070606050%33%17%0%

  

4 REPLIES 4
ImadZidan
12 - Quasar

Hello @bebrown ,

 

Are you thinking of something like this.

 

Just an idea.

 

Hope you find it helpful.

echuong1
Alteryx Alumni (Retired)

I believe you're looking for something like this?

 

echuong1_0-1598283900727.png

 

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @bebrown,

 

If you wanted to keep it truly dynamic you would want to go down the route of pivoting, then calculating percentages before finally re-pivoting back to it's original form:

 

Jonathan-Sherman_0-1598283846585.png

 

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my worklfow for you to download if needed.

 

Regards,

Jonathan

bebrown
5 - Atom

Thanks Jonathan! 

Labels