Calculate % Difference - on Calculated Items - Cube Source

[kado]

IBMions,

I am seeing a weird issue in 8.4 whereupon the 'Calculate' feature (when you click on 2 measures) specific to getting a '% Difference' for those 2 measures throws a /0 (div by zero) when applied to measures that have are being calculated in the report. So when the feature is used directly against measures from the cube, it works correctly. But when I try to apply it to measures (that I have calculated based on other measures in the report) it fails and throws the /0.

I tried using 'Data Items' for my calcs as well as 'Calculated Measures' but did not have any luck.

Anyone have any thoughts?

thanks,
Kado

PS. This is within a crosstab :)

[MFGF]

Hi,

What approach are you using for your expression?  Does your expression account for 0 or null values in the divisor?

Regards,

MF.

[kado]

Hello,

Here is the expression:

([Month Prior] - [Month Closed]) / [Month Closed]

Within a crosstab as applied to specific measures (ie Net Product Sales, Collaborative Revenue (which are also calculated data items))

Month Prior = 68
Month Closed = 61
Result = 0

The only thing I have changed (which I switched back to DEFAULT to test) was switching the Data Format to Percent (and changing the Div by Zero option to 0).

What's odd is that both columns have data so there shouldn't be any need to account for 0s or nulls in the divisor. Are you thinking something specific in the expression should be defined or something within the properties?

What's also odd is that it seems to work fine for measures directly out of the cube. But fails for calculated data items or Calculated Measures.

Thanks for the Feedback!!!
Kado

[MFGF]

Hmm - ok.  A couple of things to try:

1. Change the solve order of your calculation - set it to 2 for example, so it is solved after the other component calculations are solved.

2. Change the expression to explicitly trap zeros:
if ([Month Closed] > 0) then (([Month Prior] - [Month Closed]) / [Month Closed]) else (1)

MF.

[kado]

THANK YOU Much!!!

That 'solve order' has fixed the issue (and something I wasn't familiar with)!

;D