We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

taking average only if its not null

BosKev
8 - Asteroid

Hi All,

 

I want to take an average of 3 fields only if it has value in it, if it shows Null then average only the available numbers ( 2 of 3 has value then I only want the average of those 2)

 

is there an easier way to do this other than a if statement?

 

Thanks,

BosKev

 

 

Example:

Global Fee GroupType1Type2Type3Ideal Output 
CNull750Null750
BNullNullNull0
C37562500Null3128
B3400540066155138

 

6 REPLIES 6
agrawaluk
8 - Asteroid
JosephSerpis
17 - Castor
17 - Castor

Hi @BosKev I mocked up a workflow let me know what you think? 

BosKev
8 - Asteroid

Thank you both, but @JosephSerpis solution has fewer steps.

Igor_Garlowski
7 - Meteor

In this case the author didn't have any zeros so this solution works; what if we have zeros and we want to not count only nulls?

john007
5 - Atom

Than you so much for this.   I had a similar issue and was able to solve it with minor tweaks to your workflow.  Much appreciated.   Happy New Year!

Bilbottom
7 - Meteor

This is not the 'Alteryx way' to do this, but you can also do this with the formula tool -- but it'll get messy and long which several variables.

 

For this approach, define the following columns:

 

numerator (Int64)

0
+ ToNumber([Type1])
+ ToNumber([Type2])
+ ToNumber([Type3])

 

denominator (Int64)

-1 * (0
    + !IsNull([Type1])
    + !IsNull([Type2])
    + !IsNull([Type3])
)

 

average (Int64)

IF [denominator] = 0

    THEN 0

    ELSE [numerator]/[denominator]
ENDIF

 

You can also call the columns anything you want, but the average column above gives you what you want.

 

Note that ToNumber will implicitly replace Nulls with 0, IsNull returns -1 when True (hence the multiplication by -1), and defining average as Int64 will round to the nearest integer.

 

Bilbottom_0-1620752576103.png

 

Labels