May 14, 2008 at 11:09 am
I have a report that currently performs a calculation in which the delta between two values separated by time are compared to determine the percentage increase or decrease from the prior time periods value. The problem I have is what to do when the prior time periods value is zero and thus resulting in a divide by zero error. I've included some custom code to default this to zero but my question is more about what logically to display for the percentage. Example:
Week 1 - 0
Week 2 - 5
(0 - 5) / 0 = Undefined
There is definitely a difference between zero and five but the challenge here is how to quantify that percentage...
Any ideas?
Ben Sullins
bensullins.com
Beer is my primary key...
May 14, 2008 at 11:15 am
Division by 0 is an unknown value, so you can't "value" that. There is no "correct" mathematical representation of that percentage.
You'll need to somehow prevent that form happening. You would need something to do a CASE or IIF statement to kick out some invalid value to prevent the error.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 11:24 am
Thanks Matt...What I've done to prevent an error from occurring is included the following custom function that is called to perform the divide operation. So when it is encountered I show 0 rather than #Error...
Public Function Divide(ByVal Numerator As Double, ByVal Denominator As Double) As Double
Dim Quotient As Double
If IsNothing(Denominator) Or Denominator = 0 Then
Quotient = 0
Else
Quotient = Numerator / Denominator
End If
Return Quotient
End Function
'Example call: =Code.Divide((Fields!Current_TransactionCount.Value - Fields!Prior_TransactionCount.Value),Fields!Prior_TransactionCount.Value)
Ben Sullins
bensullins.com
Beer is my primary key...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply