NUMBER (value, format)

Description:

This function converts the string value to a number. The format parameter determines the returned number format. Two values can be supplied for format "%d" and "%f". "%d" will return the ordinal number of value. Any decimal values will be truncated. "%f" will return the value as a decimal value. The entire precision of the string will be maintained. If the value parameter is already a number this function will return value. If the value parameter is a string that cannot be converted to a number #VALUE will result.

Syntax: Number (value, format)

• Value : Any value that is specified as a string between quotes ("").
• Format : The format parameter can be either "%d" or "%f ".
        %d = will return the result without any decimal values (if such decimal values exist.)
        %f = will return the result with the decimal values (if such decimal values exist.)

Remarks

• The number argument is compulsory.
• If a text value is specified instead of a number value, then PowerOLAP returns a "0" as the result.

Example

The Number function can be used in any database where you have stored a value which you want to convert into a number format, either a number with or without decimals then, it is possible to do that with the help of this function.

This function displays the given value as an Integer in the format specified as the second parameter given to the formula.

=NUMBER ("3.99", "%d")	- Number function returns (3), i.e..; the given parameter without decimal values as the option specified is %d.
=NUMBER ("4.5", "%f")	- Number function returns (4.5), i.e..; the given parameter with decimal values as the option specified is %f.

 

Let us take the example of a cube in PowerOLAP which has data which is the information in text / number for which you want to calculate the number value in a specified format. If in the same dimension in another member or in another dimension in another cube, you want to display this calculate values for the number parameter supplied to the function, and based on the format options then it is appropriate to use the NUMBER function to calculate this value. In this case we can write a formula in the cube in PowerOLAP to derive the value for the member that would store the derived value in a number format.

 

The first formula in the screen above writes values into the NumFunctionValuesData" cube to the "WholeNumber" member in the NumberType dimension. This is done by calculating the member with the number given to the formula, without the decimal part of the number (%d). The second formula in the screen above writes values into the "NumFunctionValuesData" cube to the "DecimalNumber" member in the NumberType dimension. This is done by calculating the member with the number given to the formula, and including the decimal part of the number as well in the number (%f).