Chart AVG seems to include null values

  • 1
  • Problem
  • Updated 3 years ago
  • Not a Problem
AVG function in charts seems to assign value to null... or something?

Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb

Posted 3 years ago

  • 1
Photo of J.

J., Official Rep

  • 7,470 Points 5k badge 2x thumb
Matt,

Are you sure that there aren't nulls in your model data? I'm guessing that you are splitting on a Picklist or Number field. If you switch to a Template split and try something like this...
{{#MySplitFieldName}}{{MySplitFieldName}}{{/MySplitFieldName}}{{^MySplitFieldName}}N/A{{/MySplitFieldName}}
...do you get "N/A" instead of null?
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
J.

There ARE null values in my data (as the chart shows... the orange series is counting nulls).

But the way that AVG is handling null values seems wack. Shouldn't it either skip them or treat them as 0? There's no way that the AVG of the fields in the "Abort" category shown above should work out to 4.8x10^38.
Photo of J.

J., Official Rep

  • 7,470 Points 5k badge 2x thumb
Ah! Now I get you, and yes, it looks like AVG is effectively counting nulls as zeros. We should give you the option to count them or not count them like we do with Table summaries. I'll log this issue, and we'll get it addressed in a future update. Thanks!
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
J.

I'm not sure it's counting them as zeros. An average with a lot of zeros would make a very small number. In this case I'm getting a very large number!
Photo of J.

J., Official Rep

  • 7,470 Points 5k badge 2x thumb
Man, I'm just striking out on intelligent responses to community posts today. Slowing down, and formulating intelligent response now...

Is that data field that you are averaging a Number field or a Picklist/String field? If the latter, I think it's concatenating the values as strings (i.e. '1' + '2' + '3' = '123'). This would lead to a very big number very quickly. Average is still counting nulls as 0s, but the number is so large, it doesn't really matter. Try creating a UI-Only Formula Field that converts the string value to a number ({{MyPicklistFieldName}}*1) or if you need to use this value on multiple pages, a Salesforce formula field would be more scalable. Use this new field as your Data Field for that series. It will still count the nulls as 0s, but this may be what you want anyway. 

I'll look at making the Chart aggregate functions more intelligent from our end too (i.e. why would you ever want the AVG function to concatenate instead of add?).
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,704 Points 20k badge 2x thumb
hehehehe ... same reason I'm not trying on this. I know I'd strike out right now. I'd have to stop to study it.
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
J. Looks like we are concatenating the text values of a picklist field. Thanks!