December 6, 2012 at 9:21 am
I have an expression as follows:
=SWITCH(SUM(Fields!TotalSales.Value) = 0 AND (SUM(Fields!Weight.Value) <> 0 OR IsNothing(SUM(Fields!Weight.Value))),0,
SUM(Fields!TotalSales.Value) <> 0 AND SUM(Fields!Weight.Value) <> 0 AND NOT IsNothing(SUM(Fields!Weight.Value)) AND
SUM(Fields!Qty.Value) <> 0 AND NOT IsNothing(SUM(Fields!Qty.Value)),SUM(Fields!TotalSales.Value) / (SUM(Fields!Weight.Value)*SUM(Fields!Qty.Value)))
I'm trying to find the price per tonne based on there being a sales value, a weight (of the item in tonnes) and a quantity sold. So my basic calc is:
Sales Value / (Weight * Qty)
I need to return a zero when the sales value is £0 and the weight is neither zero nor nothing as this represents delivered tonnes at £0 (i.e. samples).
I only want to run my basic calc when the sales is not zero and when the weight and qty are neither zero nor nothing - which is what I think I have in my expression....but I still get divide by zero errors?
Any help would be appreciated.
Thanks,
jag7777777
December 7, 2012 at 2:44 am
Just in case this is useful for anyone else, resorted to VB code in the end. Really don't understand why the switch didn't work...?
This is the function:
'**********************************************************************************
'Function to prevent divide by zero errors.
'
'Returns the numerator when the denominator is 0 and the numerator is neither 0
'nor nothing
'
'**********************************************************************************
Function Divide(ByVal dbleNumerator As Double, ByVal dbleDenominator As Double) As Double
If (dbleDenominator = 0 Or IsDBNull(dbleDenominator)) And dbleNumerator <> 0 Then
Divide = dbleNumerator
ElseIf (dbleDenominator = 0 Or IsDBNull(dbleDenominator)) And (dbleNumerator = 0 Or IsDBNull(dbleNumerator)) Then
Divide = 0
Else
Divide = dbleNumerator / dbleDenominator
End If
End Function
Seems to work OK.
jag7777777
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply