Avoid division by zero replacing 0 with null
4 December, 2013
My problem is that I need to calculate a percentage variation (Var %) of 2 fields, avoiding division by 0.
Field 1 is the revenue of current year (CY), field 2 is the revenue of past year (PY).
To achive this I've used Subquery functionality like explained here
So, in a subquery I've retrieved CY value, and in another subquery I've retrieved the PY value
(in the master query I've retrieved other fields that are not usefull for this discussion).
To show the Var %, in the master query I've used a calculated field with this operation:
[code]((CY*100)/PY) - 100[/code]
My problem is that if PY is 0 there will be a division by zero.
I've read this post on YF forum and I'm agree to the fact that with a case-when statement all should be fine, but I need of a case-when like this:
[code]case when PY = 0 than ((CY*100)/PY) - 100 else null end[/code]
the issue is that I can't put null value because it will be "translated" in
[code]'null'[/code]
with single quote before and after, so is treated like a string and not as sql key.
I've tried also the creation of a custom function like described here but using custom function I can't include in the calculation a field that belong to another subquery.
How I can manage this?
Let me know if you need further info.
Thanks in advance for any suggestion.
Carlo
Field 1 is the revenue of current year (CY), field 2 is the revenue of past year (PY).
To achive this I've used Subquery functionality like explained here
So, in a subquery I've retrieved CY value, and in another subquery I've retrieved the PY value
(in the master query I've retrieved other fields that are not usefull for this discussion).
To show the Var %, in the master query I've used a calculated field with this operation:
[code]((CY*100)/PY) - 100[/code]
My problem is that if PY is 0 there will be a division by zero.
I've read this post on YF forum and I'm agree to the fact that with a case-when statement all should be fine, but I need of a case-when like this:
[code]case when PY = 0 than ((CY*100)/PY) - 100 else null end[/code]
the issue is that I can't put null value because it will be "translated" in
[code]'null'[/code]
with single quote before and after, so is treated like a string and not as sql key.
I've tried also the creation of a custom function like described here but using custom function I can't include in the calculation a field that belong to another subquery.
How I can manage this?
Let me know if you need further info.
Thanks in advance for any suggestion.
Carlo
Hi Carlo
You are correct it is not possible to add calculated fields into a Custom Function.
An enhancement request has been raised for this to be considered for future releases. (TASK # 150664)
Another enhancement has been raised for the value NULL to be added to Calculated Field Builder. (TASK # 150674)
As a temporary work around i manage to replicate your problem and found a workaround.
Firstly i created a dummy virtual table that only returns null values, using the primary key of a different table at the view level and joined it to that table.
Also add the new column NullColumn to your list of available columns.
Then at the Report Level you create a calculated field that returns the Null column when your Previous Year amount is 0
This should prevent the division by zero error and also give you a Null value instead.
Just hide the NullColumn in your Report Output.
Hope this helps you achieve what you trying to do.
Regards,
Stephen
You are correct it is not possible to add calculated fields into a Custom Function.
An enhancement request has been raised for this to be considered for future releases. (TASK # 150664)
Another enhancement has been raised for the value NULL to be added to Calculated Field Builder. (TASK # 150674)
As a temporary work around i manage to replicate your problem and found a workaround.
Firstly i created a dummy virtual table that only returns null values, using the primary key of a different table at the view level and joined it to that table.
Also add the new column NullColumn to your list of available columns.
Then at the Report Level you create a calculated field that returns the Null column when your Previous Year amount is 0
This should prevent the division by zero error and also give you a Null value instead.
Just hide the NullColumn in your Report Output.
Hope this helps you achieve what you trying to do.
Regards,
Stephen
Thanks a lot.
After some tests seems that your workaround works.
Thanks,
Carlo
After some tests seems that your workaround works.
Thanks,
Carlo