asked on August 5, 2024
Hello,
I'm calculating the salesnumbers over a period of time and then display them in a line graph.
In my provided screenshot I'm comparing three different products.
The first (yellow) line has values for each month and thus shows a full line for all of 2024 so far.
The second (red) line starts two months later, as there were no sales in January/February.
The third (blue) line misses a value in August since there hasn't been any sale yet.
Is it possible to display those missing values as 0 in the line graph? I'd imagine a check in a calculated measure to replace any NULL with a 0.
With kind regards,
Florian
⋅ August 5, 2024
Hello, Florian!
Thank you for reaching out to us.
Kindly note that it is possible to add an if statement in Calculated Values to replace missing values with 0, e.g.:
if(sum('Price') > 0, sum('Price'), 0)
We have prepared a JSFiddle sample to illustrate the idea: https://jsfiddle.net/flexmonster/mv39n1ew/.
Hope you will find our answer helpful.
Kind regards,
Solomiia
⋅ August 5, 2024
Hello Solomiia!
Thank you for your quick reply.
So I tried to apply your proposal like this:
IF((sum("ISNULL(fi.mAmountInv, 0)") < 0) or (sum("ISNULL(fi.mAmountInv, 0)") > 0), sum("ISNULL(fi.mAmountInv, 0)"), 0)
So that if my aggregated sum of mAmountInv is < 0 or > 0 the function provides this result. Otherwise it returns 0.
If I understand correctly this should provide "0" for any "real" zeroes and NULL values?
Unfortunately this does not seem to work. (see attached screenshot)
Please note that we use a custom datasource (but still a sql database).
⋅ August 5, 2024
Hello, Florian!
Thank you for your swift response.
Kindly note that you can adjust the condition in if statement to match your use case. For example, you can use !isNaN to check for empty values:
if(!isNaN(sum('fi.mAmountInv')), sum('fi.mAmountInv'), 0)
Please check out the list of available operators in our Calculated Values by the link: https://www.flexmonster.com/doc/calculated-values/#formula-operators.
Hope it helps.
Kind regards,
Solomiia
⋅ August 5, 2024
The isnan/!isnan operators were what I was looking for 🙂
Unfortunately even they don't yield a result...
if(!isnan(sum("ISNULL(fi.mAmountInv, 0)")), -sum("ISNULL(fi.mAmountInv, 0)"), 0)
⋅ August 5, 2024
Hello, Florian!
Thank you for getting back to us.
Kindly note that the correct syntax for the formula in our case is:
IF(!isNaN(AggregationFunction('FieldUniqueName')), (-1)*AggregationFunction('FieldUniqueName'), 0)
Please check that the uniqueName of the field in the formula matches the one present in the data, e.g., in custom data source API, all fields are sent to Flexmonster with the /fields request.
If you need our further assistance with configuring the calculated value formula, please modify the following JSFiddle to help us reproduce the case on our side: https://jsfiddle.net/flexmonster/3u92tago/.
Looking forward to hearing from you.
Kind regards,
Solomiia
⋅ August 6, 2024
Good morning Solomiia,
I know that our FieldUniqueName looks weird, but that's what we have been using for quite a while now without issues (never hindered any aggregate function or usage in calculated measures).
As this starts to seem more like a technical issue rather than an error in usage, I will refer our developers to this thread in the hopes that they can find the issue.
Thank you very much for your assistance so far!
Kind regards,
Florian
⋅ August 7, 2024
Hello, Florian!
Thank you for the updates.
Do not hesitate to reach out to us if any further questions arise.
Best regards,
Solomiia
⋅ August 14, 2024
Hello, Florian!
Hope you are doing well.
Our team is wondering if there are any updates on the case. Could you please let us know if you need our further assistance in configuring calculated measures in Flexmonster?
Looking forward to hearing from you.
Kind regards,
Solomiia
⋅ August 19, 2024
Hello Solomiia,
thanks for reaching out again!
I was able to locate the issue - which is 100% on our part.
So I will mark this ticket as resolved.
Again, thank you and your team very much for your assistance!
⋅ August 20, 2024
Hello, Florian!
Thank you for your feedback.
We are glad that the case is resolved now.
Do not hesitate to reach out to us if any other questions arise.
Best regards,
Solomiia