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
⋅ 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: https://www.flexmonster.com/doc/managing-data-presentation-json.
Let us know if everything works fine for you.
Best regards,
Dmytro.
⋅ May 23, 2017
Thanks, that did the job.
