Calculate min, max and mean values for a category

I am attempting to recreate some excel analysis in Alteryx.

I have a dataset containing public accounts data data approx. 5,000 companies. Amongst the data are the compound average growth rate (CAGR) and the industrial SIC code.

I would like to identify "outlier" CAGR % (both max and min) for each SIC code, and also identify the companies with the max and min CAGR in order that these can potentially be excluded if required from final analysis.

Attached is an extract showing how the data is currently presented in Excel. The min/max/average are calculated using an Averageif/MaxIf/MinIf, and the company with the max/min CAGR looked up using an X-Lookup.

Can anyone help with a way to calculate the Min, Max and Average for each Industrial sector, and then how to identify the max/min companies? I am guessing the Summarize and then Fuzzy Match tool, but some guidance would be greatly appreciated.

David

CAGR extract.xlsx

Reporting

Data Investigation

Accepted answers

smoosh

Here's a similar workflow as designed by @jdminton.

I also added a 'Z' score as handy way to help identify outliers as well as a rank for each company in each category both 3 and 5 years.

Edit: As a side note, some of the categories in the data only have 1 or 2 companies. The 'Z' scores, and values there are going to be missing or next to useless.

SIC Rankings.yxmd

All comments

jdminton

You wouldn't want to use fuzzy match for this type of task. The summarize tool can provide Min, Max and Avg. Summarize the data grouped by industry SIC with min max and average on the 3 or 5 yr (or both) and after the summarize, join back to the original data with the join on industry SIC. After that, you can do additional sorting or analysis as needed to determine your outliers.

SIC Group.yxzp

smoosh

Here's a similar workflow as designed by @jdminton.

I also added a 'Z' score as handy way to help identify outliers as well as a rank for each company in each category both 3 and 5 years.

Edit: As a side note, some of the categories in the data only have 1 or 2 companies. The 'Z' scores, and values there are going to be missing or next to useless.

SIC Rankings.yxmd

D0Chapman

@jdminton and @smoosh - this is excellent. Thank you both. I really appreciate the help and advice.

David

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703