Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Z Score Macro


ZScore Meme.jpg


What is a Z Score?


It’s a measure of how many standard deviations below or above the population mean a raw score is. It is a way to compare results from a test to a normal distribution.



How is Z Score Calculated?


Z score calculations contain three simple variables


X = observed raw score

M = Mean of the test

SD = Standard Deviation of the test



The Problem I encounter when trying to apply this in Alteryx is, what if I have multiple tests for which I want to calculate the Z Score in a single data-set? Instead of doing the same repeated steps over and over for each test. I went ahead and created a macro.



How the Macro Functions:


Example.PNGWhen feeding in a dataset to the Z Score Select Macro, you are prompted to select two options. The first is the “Grouping Fields” – This will be the columns in your dataset that we will then calculate the average and standard deviation on. It can be one or multiple.


For example; I have an experiment that I am holding test every week (1-52) over 20 years. In my dataset I have 2 columns, one for week and one for year. If I want to find the observed Z Score for weeks 1-52 in 2018, my grouping fields will be both columns; week and year. If I wanted to calculate an observed Z Score for week 1 in 2010, 2011, and 2020; only week will be my grouping field. Alternatively, if I wanted to calculate an observed Z Score for every test in a given year, only year will by my grouping field.


The Second option is prompting you to select the fields you want to score. These will be the columns that contain your observed or tested values. You can select one or multiple.




The output will append a new column containing the Z score for every observed record. From there you can do as your heart desires!


Super helpful, thanks @NicholasM !


This macro is sweet. Is it on the public gallery? Had trouble finding it.