Despite the COVID-19 outbreak, our team continues operating at full speed. We are always here to support and answer all your questions.

Feel free to reach out by filling this quick form.

Fill the form
Get Free Trial

How to exclude nulls from a calculated average?

Ben Shenton asked on May 19, 2017

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