Calculated Measure: Wierd scaling issue when subtracting two decimal numbers

I have a calculated measure which is a simple difference between a measure on consecutive dates. It seems to be working fine, except that the original numbers have a scale of 2 decimal points (they are currency measures).

This is the calculation part of the MDX expression:

([BusinessDate].[Date].CurrentMember, [Measures].[GBPEQUIV-SENSITIVIES])

-

([BusinessDate].[Date].CurrentMember.Lag(1), [Measures].[GBPEQUIV-SENSITIVIES])

eg: 2,588,829,302.21 - 2,572,404,828.64

The real answer is -16,424,473.57

However, the actual value of the calculated measure is -16,424,473.5770621 which has a higher scale than the actual numbers and is in fact inaccurate.

I presume it is doing some sort of floating point calculation but this is just wrong!

How can I fix this?

Do the actual values being loaded in from the underlying fact table only have 2 decimal points of precision? Or is the 2 decimal point precision simply set as a display format for the measure in SSAS? If the former, then I'm not sure why the value would be inaccurate. If the latter, however, the actual values being stored in the measure may contain more precision (if the underlying values from the fact table do) and you would thus be seeing the result of the calculation using the higher precision...

HTH,

Dave Fackler

|||Like Dave, I suspect the formatting, rather than precision of calculation, since Currency performs exact operations on 4 digits after the digital point. Can you specify FORMAT_STRING='Currency' for your calculated member.|||

Underlying data from Oracle has a data type of NUMBER(22,4) for these measures.

Yes they can be formatted as currency.

Even so, when doing a simple difference between two numbers with a scale of 4, the result cannot be at a greater scale, surely?

|||

When you look at the properties of the measure in the cube designer, what data type is listed for the source of the measure?

And what aggregation function (assuming Sum, but want to verify) is listed for the measure?

Dave Fackler

|||What I am saying is that the result is most likely correct, but the visual representation (formatted value) looks different because the input measures and calculated measure have different formattings.|||

Orignal data type from database is as above. In the DSV it is System.Decimal (in the XML it is xs:decimal), in the cube the data type is set to inherited.

Aggregation function is Sum.

Calculated Measure Error

In the calculated measure formula when something is being divided by 0.XX (like 0.35..fractions) its producing those wrong figures where decimal is at a wrong place. But if we divide the numerator and denominator by 100, we get the correct figures.

[something].[Something] *100 / 0.XX *100 = good

[something].[Something] / 0.XX = wrong

Why is that? and what can be done to do it right. Thanks.

Are you using AS 2000 or AS 2005, and can you reproduce this problem with one of the standard cubes (like Foodmart or Adventure Works)?

calculated field question

I'm trying to do a calculation on a couple of number and
return a decimal number. An example of what I'm trying
to do is:
select (44-2)/44
How can I make this return 0.96
TIA,
Vic
Hi,
select round((44-2)/44.0,2)
Thanks
Hari
MCDBA
"Vic" <vduran@.specpro-inc.com> wrote in message
news:2472501c45f6c$b91a1ee0$a401280a@.phx.gbl...
> I'm trying to do a calculation on a couple of number and
> return a decimal number. An example of what I'm trying
> to do is:
> select (44-2)/44
> How can I make this return 0.96
> TIA,
> Vic

calculated field question

I'm trying to do a calculation on a couple of number and
return a decimal number. An example of what I'm trying
to do is:
select (44-2)/44
How can I make this return 0.96
TIA,
VicHi,
select round((44-2)/44.0,2)
Thanks
Hari
MCDBA
"Vic" <vduran@.specpro-inc.com> wrote in message
news:2472501c45f6c$b91a1ee0$a401280a@.phx
.gbl...
> I'm trying to do a calculation on a couple of number and
> return a decimal number. An example of what I'm trying
> to do is:
> select (44-2)/44
> How can I make this return 0.96
> TIA,
> Vicsql

calculated field question

I'm trying to do a calculation on a couple of number and
return a decimal number. An example of what I'm trying
to do is:
select (44-2)/44
How can I make this return 0.96
TIA,
VicHi,
select round((44-2)/44.0,2)
--
Thanks
Hari
MCDBA
"Vic" <vduran@.specpro-inc.com> wrote in message
news:2472501c45f6c$b91a1ee0$a401280a@.phx.gbl...
> I'm trying to do a calculation on a couple of number and
> return a decimal number. An example of what I'm trying
> to do is:
> select (44-2)/44
> How can I make this return 0.96
> TIA,
> Vic

But the documentation is NOT correct

The documentation says

ISNUMERIC returns 1 when the input expression evaluates to a valid
integer, floating point number, money or decimal type; otherwise it
returns 0. A return value of 1 guarantees that expression can be
converted to one of these numeric types.

(Cut and pasted from books online)

Yet the following, one of many, example shows this is not true.

select isnumeric(char(9))
select convert(int, char(9))

----
1

(1 row(s) affected)

Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value '' to a column of data type
int.

So, besides filtering every possible invalid character, how do you
convert dirty values without error. I am not concerned that I may loose
possibly valid values or convert suspect strings to 0 (zero). I just
want to run without raising an errorBooks Online is quite correct here, it just could be a bit clearer. The
significant word in the paragraph you posted is "or". ISNUMERIC returns
1 if the data is convertible to ANY of the datatypes listed. Try the
following, which will work:

SELECT CONVERT(MONEY, CHAR(9))

If you just want to convert positive integers then you can use LIKE to
determine whether a string contains only numerics:

SELECT CAST(col AS INTEGER)
FROM YourTable
WHERE col NOT LIKE '%[^0-9]%'

--
David Portas
SQL Server MVP
--|||By inference then CONVERT(MONEY, x) is the most 'tolerant' conversion.
Strings containing only numerics is also elegant.

Thanks for the tip|||(bilbo.baggins@.freesurf.ch) writes:
> By inference then CONVERT(MONEY, x) is the most 'tolerant' conversion.

Not necessarily:

SELECT convert(money, '1E1')

bombs. But isnumeric() returns 1.

isnumeric is a useless function.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp