Divide by Error

[jasp]

Hi All,

any help on the below.

Calculation one = Unit price Current Year
I have a measure on a report say Sales and also a measure which has quantity and then a data item calculating unit price = Sales / Quantity.

Calculation two = Unit price prior Year
= prior year sales / prior year quantity (both measures nested with the current month)

Calculation three = Variance in the Unit Price.

If i have quantity for both calculations then the report works fine, however if i have no quantity for a period then i get in the variance column /0 sign .

Does any body know how to calculate the variance correctly even if there is a zero quantity for one of the calculations. The format option does not work.

= Difference in the unit price ie Calc one less Calc two

[PRIT AMRIT]

Variance should be: ([Current Year]-[Prior Year])/[Prior Year)*1

Select the Variance field in report, in properties -- Data Format-- Set it to 'Percent' --- You can see an option 'Divided by Zero value' character, set it to '-' or how u wish to display.

Hope this resolves?

Thanks
Prit 

[jasp]

It did not work.......

Say in one calc i have the 10 and the second i have 5 then the variance should be 5.

However if there is no quantity in the first calc it is givinmg me a /0 and because i am using this in my calcuation in another item it displays /0

Any help

[Lynn]

How about changing the underlying calculations to use a conditional statement:

Code Select Expand


if ( [Quantity] = 0 )
then ( 0 )
else ( [Sales] / [Quantity]


[jasp]

thanks it worked, sometimes second set of eyes is better.

Help is much appreciated.
Just started my journey into BI

[Lynn]

Glad it worked out :)

[jasp]

It worked for a simple query but not for a complex query i have , does anyone know if i have a calcuation like this how to resolve it...

if i have Sales/ Quantity but quantity can be zero can you have some type of syntax which ignores the error and puts in a value in the calculation.

Sorry if this is not clear

[Lynn]

Can you describe what is complex about the query and what doesn't work? Do you get an error or just incorrect results? What is the expression?