Menu
Download Free Trial

How to exclude nulls from a calculated average?

Hi,
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:
F_STATUS_6    F_TERM
0                         0
5                         36
8                         24
0                         0
1                         36
 
Average = 32 not 19.2
 
Any help will be appreciated.
 
Thanks
 
Ben
 

2 answers

Public
Dmytro Zvazhii Flexmonster May 22, 2017

Hello Ben,
Thank you for your question. We recommend you replacing zeroes with the null values next way: 
F_STATUS_6,-F_TERM
0,null
5,36
8,24
0,null
1,36

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: http://www.flexmonster.com/doc/data-types-in-json/.
Let us know if everything works fine for you.
Best regards,
Dmytro.

Public
Ben Shenton May 23, 2017

Thanks, that did the job.
 

Please login or Register to Submit Answer