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

Need dynamic formula to create average and standard deviation data

Hi! I have a data set with lots (30+) metrics and multiple data names in the Field1 column. I know I could use a Summarize and basic Formula tool to calculate the Average, Standard Deviation, and Coefficient of Variation. However, there are many metrics and new ones could be requested. I tried to use the CReW Dynamic Formula macro but I don't think it's working because my field names are not unique. Any thoughts on a more dynamic approach?

 

Raw Data

MichelleMitchellLutz_0-1651848114343.png

 

Desired Outcome

MichelleMitchellLutz_1-1651848130158.png

 

Not successful CReW Macro output

 

MichelleMitchellLutz_2-1651848335492.png

 

 

 

2 REPLIES 2
KilianL
Alteryx Alumni (Retired)

Hi @MichelleMitchellLutz ,

 

the most useful tool for dynamic workflows is the Transpose, often combined with the Summarize and Cross-Tab.

By transposing all columns except Field1, the option 'dynamic and unknown columns' makes sure to transpose any new Metrics in your dataset. The Summarize is able to get Avg and StdDev for each metric.

 

Find an example attached.

 

 

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx

Thank you so much! I couldn't wrap my mind around it, but this is perfect. Have a great weekend!

Labels