How to exclude nulls from a calculated average?
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:
Average = 32 not 19.2
Any help will be appreciated.
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: http://www.flexmonster.com/doc/data-types-in-json/.
Let us know if everything works fine for you.
Thanks, that did the job.