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 Average function to ignore null values

justin_norris
5 - Atom
I am attempting to find the average of given fields. The Average function is returning the average, but not how I need it. Some of the fields have null values and I need the function to ignore them. For example: If I'm finding the average of 7 fields and 1 is null, the sum should be divided by 6 instead of 7. 

The null field could be any of the 7. 
5 REPLIES 5
mbarone
16 - Nebula
16 - Nebula

For me personally, since the Summarize Tool has no "average - ignore nulls", I update the nulls to zero before the Summarize Tool (then you can use the "average - ignore zeros") using either the Formula Tool or the Multi-Field Formula Tool (depending on how many fields I'm updating).  

For the formula itself, if you use "ToNumber([FieldName])" just like that, and the field is numeric, then it will fill the nulls with zeros.  Or, depending on my need, I may filter out the non-nulls and then use the Summarize Tool with just those non-nulls.

justin_norris
5 - Atom
That's a good suggestion, but I left out a key piece of info. I am working with a 0-10 points system. A zero means they scored very poorly (obviously) and should be included in the calculation. If the field is null, that means a score wasn't reported and should not be included in the calculation. Nulls must be carried through as nulls for reporting purposes. 

I could write a ton of nested If statements, but there are up to 13 fields needed in the calculation. I would need an If statement for every possible combination...there has to be an easier way.

 
AdamR_AYX
Alteryx Alumni (Retired)
OK well if all your values are 0-10 then how about this for a solution:

  • Use a multi-field formula to update all of your values with this expression:
iif(isnull([_CurrentField_]), 0, [_CurrentField_] + 1)

i.e. set nulls to zero and increase every other value by 1.
  • Then use the summarise tool with "average - ignore nulls" for all of your fields.
  • Then use a second multi-field formula to update all of the newly created averages with this expression:
[_CurrentField_] - 1

Which subtracts the one we added to all of the values in the first expression.

Adam
Adam Riley
https://www.linkedin.com/in/adriley/
doug_helmreich
5 - Atom

Apparently fall is the time to try to take averages inclusive of null values. AdamR's solution works for single fields, but if you want to take an average across multiple fields you need to first transpose the data, then summarize, then merge back in -- a pain. SPSS has a simple mean.x function that allows you to specify the maximum number of missing values that will allow it to return an average without error. Would love a similar functionality in Alteryx.

MarqueeCrew
20 - Arcturus
20 - Arcturus

Maybe you can post this as an idea on the ideas board?  Maybe with some guidance, you and I could create a macro for the community to benefit from. I'd be happy to work with you on it. 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels