Get Free Trial

How to exclude nulls from a calculated average?

Ben Shenton asked on May 19, 2017

I have added a calculated field to give me an average of a column, the column contains some nulls which Flexmonster doesn’t seem to cope with very well.
If I replace the NULLs in the raw data with zeros, flexmonster includes these within the average which I don’t want.
There is no option to remove the records in question as other columns are used within the JSON.
I did try adding an IF to my formula, if a value in the F_STATUS_6 column is greater than 0 then I know there will be a value in the F_TERM column
e.g. if(sum(“F_STATUS_6”) > 0, average(“\”F_TERM”\)”)
All this does is give me an average for all values within F_TERM when true, when I expected to only include the records where the statement is true:
0                         0
5                         36
8                         24
0                         0
1                         36
Average = 32 not 19.2
Any help will be appreciated.

2 answers

Dmytro Zvazhii Dmytro Zvazhii Flexmonster May 22, 2017

Hello Ben,
Thank you for your question. We recommend you replacing zeroes with the null values next way: 

Please note that the data above is the example for .CSV datasource. The minus sign “-” before the column name helps the component to understand that the column has numeric values.
If you use the JSON datasource you should pre-define the header object which includes the types of your data columns. Please find the example here:
Let us know if everything works fine for you.
Best regards,

Ben Shenton May 23, 2017

Thanks, that did the job.

Please login or Register to Submit Answer