asked on June 15, 2020
We are having an issue getting the correct figure (in our eyes) when using the aggregation difference row formula.
We have pinpointed this down to how FlexMonster treats null values, or rather records that don't exist.
Let's take the following data excerpt:
We would expect the difference between 70,000 and nothing to be 70,000, however instead we are receiving nothing.
Unfortunately, we cannot replace the nothing value with zero, with how our data is constructed. These records just simply may not exist, so we can replace or inject a different number.
⋅ June 15, 2020
Hi Scott,
Thank you for posting your question.
Please note that this is the expected behavior since aggregations can only be calculated based on numeric values.
Since, as you've mentioned, the input data set cannot be changed to contain zeroes instead of "", what we can suggest is to implement some kind of preprocessing on the client-side, which would load the JSON data to the page and modify it in a way that all empty values are replaced with 0. This way, the initial data won't have to be modified while Flexmonster will also receive the data in a convenient format that allows calculating aggregations properly.
Do you think something like this would work for you?
Please let us know what you think.
Regards,
Mykhailo
⋅ June 16, 2020
Hi Mykhailo,
Hope you are well and staying safe!
Are you suggesting to take the data using getData() and somehow replace the nulls with zeros? If so, how would you suggest I do that?
As I mentioned, the data doesn't actually exist when creating our pivot.
⋅ June 17, 2020
Hi Scott,
We hope you are doing well too!
Please note that the getData() API call was not designed to be used for any purposes other than for integration with 3rd party charting libraries.
Instead, as a potential workaround, you can receive the JSON data on the client-side (with an AJAX request, for example), which would then pass the data to a corresponding data preprocessor.
We've prepared a quick sample illustrating the idea of data preprocessor (please note that AJAX request is not included in the sample for simplicity): https://jsfiddle.net/flexmonster/a9ydbLzo/
Please let us know if this helps.
Kind regards,
Mykhailo
⋅ June 17, 2020
Hi Mykhailo,
Thanks for the reply. Unfortunately your suggestion won't work as we don't actually have the data in the first place. I realise this contradicts my first example, but that was just to demonstrate the calculation issue we were having.
Our data is more likely to look like this:
Please note column two's calculation is clearly incorrect, because we don't have the data packet of row two, column one. Hoping you can help us solve this.
⋅ June 19, 2020
Hi Scott,
Please note that the mentioned cell being empty is the expected behavior – since there is no entry with Row ID 2 & Column Id 1, the difference of row aggregation has nothing to calculate the value on in the next column.
Still, we will investigate this further and see whether it is possible to parse such absent values as zeroes instead of nulls for the difference of row/columns formula. We will return to you with updates on this ETA Jul 27th.
As always, feel free to reach out if you have any other questions we can help you with.
Regards,
Mykhailo
⋅ July 28, 2020
Hi Scott,
Hope you're doing well.
We are happy to inform you that the issue with calculating differenceofrow and differenceofcolumn aggregation between existent and nonexistent values was fixed.
This is now officially available in the 2.8.12 version of Flexmonster: https://www.flexmonster.com/release-notes/
You are welcome to update the component. Here is our updating to the latest version guide for assistance: https://www.flexmonster.com/doc/updating-to-the-latest-version/
Please let us know if you have any questions we can assist you with.
Best regards,
Mykhailo
