Chart AVG seems to include null values

AVG function in charts seems to assign value to null… or something?

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?

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.

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!

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!


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?).

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.

J. Looks like we are concatenating the text values of a picklist field. Thanks!