47
MicroStrategy Functions Reference
Contents
© 2011 MicroStrategy, Inc.
xi
Rate (interest rate per period).............................................. 303
Received (amount received at maturity).............................. 303
Sln (straight-line depreciation)............................................. 305
Syd (sum of year’s digits depreciation)................................ 305
Tbilleq (T-bill equity)............................................................. 306
Tbillprice (T-bill price)........................................................... 307
Tbillyield (T-bill yield) ........................................................... 308
Vdb (variable declining balance).......................................... 309
Yield..................................................................................... 309
Yielddisc (yield on a discounted security)............................ 311
Yieldmat (yield at maturity) .................................................. 312
Mathematical functions.............................................................. 313
Abs (absolute value)............................................................ 314
Acos (arc cosine) ................................................................. 314
Acosh (arc cosine, hyperbolic)............................................. 315
Asin (arc sine)...................................................................... 315
Asinh (arc sine, hyperbolic).................................................. 315
Atan (arc tangent) ................................................................ 316
Atan2 (arc tangent 2)........................................................... 316
Atanh (arc tangent, hyperbolic)............................................ 317
Ceiling (ceiling value)........................................................... 317
Combine (combination)........................................................ 318
Cos (cosine)......................................................................... 319
Cosh (cosine, hyperbolic) .................................................... 319
Degrees (conversion to)....................................................... 320
Exp (exponent) .................................................................... 321
Factorial (factorial)............................................................... 321
Floor (floor value)................................................................. 322
Int (integer) .......................................................................... 323
Ln (logarithm, natural).......................................................... 324
Log (logarithm)..................................................................... 324
Log10 (logarithm, base 10).................................................. 325
Mod (modulus)..................................................................... 325
Power................................................................................... 326
Quotient ............................................................................... 327
Radians (conversion to)....................................................... 328
Randbetween (random number between two values) ......... 328
Round (round to nearest integer)......................................... 329
Round2 (round to specified precision)................................. 329
Sin (sine).............................................................................. 330
Sinh (sine, hyperbolic) ......................................................... 331
Sqrt (square root)................................................................. 331
Tan (tangent) ....................................................................... 332
47
Contents
MicroStrategy Functions Reference
xii
© 2011 MicroStrategy, Inc.
Tanh (tangent, hyperbolic)................................................... 333
Trunc (truncate) ................................................................... 333
Statistical functions.................................................................... 334
AvgDev (average deviation)................................................. 334
BetaDistribution ................................................................... 335
BinomialDistribution ............................................................. 336
ChiSquareDistribution.......................................................... 338
ChiSquareTest (chi-square test for goodness of fit) ............ 339
Confidence (confidence interval) ......................................... 340
Correlation ........................................................................... 341
Covariance........................................................................... 342
CritBinomial (criterion binomial)........................................... 343
ExponentialDistribution........................................................ 344
Fisher (fisher transformation)............................................... 345
FDistribution (f-probability distribution) ................................ 345
Forecast............................................................................... 346
ForecastV (forecast, vector input)........................................ 348
FTest.................................................................................... 350
GammaDistribution .............................................................. 351
Growth ................................................................................. 352
GrowthV (growth, vector input)............................................ 353
HeteroscedasticTTest and HomoscedasticTTest................ 355
HypergeometricDistribution.................................................. 357
Intercept............................................................................... 358
InverseBetaDistribution (inverse of the beta distribution) .... 359
InverseChiDistribution (inverse of chi-squared
distribution) .......................................................................... 359
InverseFisher (inverse of the Fisher transformation) ........... 360
InverseFDistribution (inverse of F-probability distribution)... 361
InverseGammaDistribution (inverse of gamma
distribution) .......................................................................... 362
InverseLognormalDistribution (inverse of lognormal
distribution) .......................................................................... 362
InverseNormDistribution (inverse of normal cumulative
distribution) .......................................................................... 363
InverseNormSDistribution (inverse of standard normal
cumulative distribution)........................................................ 364
InverseTDistribution (inverse of T-distribution).................... 364
Kurtosis................................................................................ 365
LognormalDistribution.......................................................... 366
MeanTTest (mean T-test) .................................................... 367
NegativeBinomialDistribution............................................... 368
NormalDistribution (normal cumulative distribution) ............ 369
45
MicroStrategy Functions Reference
Contents
© 2011 MicroStrategy, Inc.
xiii
PairedTTest (paired T-test, two-sample for means) ............ 370
Pearson (Pearson product moment correlation
coefficient) ........................................................................... 371
Permut (permutation)........................................................... 372
PoissonDistribution .............................................................. 373
RSquare (square of pearson product moment
correlation coefficient).......................................................... 374
Skew .................................................................................... 374
Slope (of a linear regression)............................................... 375
Standardize.......................................................................... 376
StandardNormalDistribution (standard normal cumulative
distribution) .......................................................................... 377
SteYX (standard error of estimates) .................................... 377
TDistribution......................................................................... 378
Trend ................................................................................... 379
TrendV (trend, vector input)................................................. 380
VarTest (variance test)......................................................... 382
WeibullDistribution ............................................................... 383
A. MicroStrategy and
Database Support for
Functions
Introduction.............................................................................. 385
Analytical Engine support for functions...................................... 385
Databases that a function can be evaluated on......................... 388
Aster nCluster...................................................................... 389
IBM DB2 .............................................................................. 396
Greenplum ........................................................................... 410
HP Neoview ......................................................................... 416
Infobright.............................................................................. 422
Informix................................................................................ 428
Microsoft Access.................................................................. 437
Microsoft SQL Server........................................................... 443
MySQL................................................................................. 452
Netezza................................................................................ 455
Oracle .................................................................................. 464
PostgreSQL ......................................................................... 481
Red Brick ............................................................................. 487
Sybase................................................................................. 491
Teradata .............................................................................. 508
Vertica.................................................................................. 516
Glossary ................................................................................... 525
5
Contents
MicroStrategy Functions Reference
xiv
© 2011 MicroStrategy, Inc.
Index ......................................................................................... 529
21
© 2011 MicroStrategy, Inc.
About this book
xv
P
REFACE
Description of guide
Functions in MicroStrategy are powerful tools used in expressions to define
MicroStrategy objects and initiate complex user-selected calculations.
Wherever you can define an expression, you can use a function. From
creating basic objects to building complex reports and analyzing data, you
have the ability to create custom expressions using a large library of
functions supported by MicroStrategy. Although functions are most
commonly used in metric expressions, they are also used to create filters,
define attribute forms, and so on.
About this book
This guide provides the following information:
• Details of function syntax and the components of a function formula
• Explanation of where functions can be used in expressions
• Steps to access functions from MicroStrategy Desktop
• Overview of the MicroStrategy engine and how Intelligence Server uses
functions
37
Preface
MicroStrategy Functions Reference
xvi
About this book
© 2011 MicroStrategy, Inc.
• Examples of functions used in complex business scenarios
• Details of function support in certified databases
This guide does not include information on Data Mining functions.
This information can be found in the Data Mining Services chapter of
the MicroStrategy Advanced Reporting Guide.
Additional formats
This book is also available as an electronic publication in the Apple
iBookstore, and can be read on an iPhone or iPad with the iBooks app
installed. To download this book, search for the book’s title in the iBookstore
search bar, or scan the QR code below using your device's camera.
Function categories
For the purposes of this guide, functions have been organized into the
following categories:
• Chapter 2, Standard Functions
Basic functions, page 92
Date and time functions, page 118
Internal functions, page 131
NULL/Zero functions, page 148
OLAP functions, page 150
Rank and NTile functions, page 201
String functions, page 221
• Chapter 3, Operators
Arithmetic operators, page 236
41
MicroStrategy Functions Reference
Preface
© 2011 MicroStrategy, Inc.
About this book
xvii
Comparison operators, page 239
Comparison for rank operators, page 249
Logical operators, page 254
• Chapter 4, Plug-In Package Functions
Data mining functions, page 258
Financial functions, page 259
Mathematical functions, page 313
Statistical functions, page 334
Function descriptions
For every function identified, the description includes:
• Information returned
• Function syntax
• Expression representative of the function (as applicable)
• Notes regarding restrictions or conditions on execution and use (as
applicable)
• Examples of the function in reports or through simple descriptions
The following sections provide the location of additional examples, list
prerequisites for using this book, and describe the user roles the information
in this book was designed for.
Dates in the MicroStrategy Tutorial project are updated to reflect the
current year. The sample documents and images in this guide, as well
as the procedures, were created with dates that may no longer be
available in the Tutorial project. Replace them with the first year of
data in your Tutorial project.
How to find business scenarios and examples
Within this guide, each function is accompanied by an example, as well as
some usage guidelines. The first chapter includes additional examples that
involve several functions in business use cases.
33
Preface
MicroStrategy Functions Reference
xviii
About this book
© 2011 MicroStrategy, Inc.
For examples of reporting functionality, see the MicroStrategy Tutorial,
which is MicroStrategy’s sample warehouse, metadata, and project.
Information about the MicroStrategy Tutorial can be found in the
MicroStrategy Basic Reporting Guide.
Detailed examples of advanced reporting functionality can be found in the
MicroStrategy Advanced Reporting Guide.
Other examples in this book use the Analytics Modules, which include a set
of precreated sample reports, each from a different business area. Sample
reports present data for analysis in such business areas as financial
reporting, human resources, and customer analysis.
What’s new in this guide
MicroStrategy 9.2.1
Any updates to this guide were minor and not directly related to
MicroStrategy 9.2.1. For a list of new features in MicroStrategy 9.2.1, see the
MicroStrategy Readme for that release.
MicroStrategy 9.2.0
• Aggregation functions such as Count include an
UseLookupForAttributes parameter. This parameter is described
along with the Count function (see Count, page 95).
• The Rank function includes a new NullInclude parameter, which lets
you determine how NULL values are represented in the Rank calculation.
This parameter is described with the Rank function (see Rank, page 217).
Prerequisites
Before reading this document, you should be familiar with:
• Basic MicroStrategy terminology like metrics, facts, attributes, and so on.
This information is found in the Basic Reporting Guide.
• Additional MicroStrategy object knowledge such as custom groups. This
information is found in the Advanced Reporting Guide.
35
MicroStrategy Functions Reference
Preface
© 2011 MicroStrategy, Inc.
Resources
xix
• Standard mathematical function notation.
Who should use this guide
This document is designed for any user who needs to create an expression
using any of the predefined functions MicroStrategy offers.
Resources
Documentation
MicroStrategy provides both manuals and online help; these two information
sources provide different types of information, as described below:
• Manuals: In general, MicroStrategy manuals provide:
Introductory information and concepts
Examples and images
Checklists and high-level procedures to get started
The steps to access the manuals are described in Accessing manuals and
other documentation sources, page xxv. Most of these manuals are also
available printed in a bound, soft cover format. To purchase printed
manuals, contact your MicroStrategy account executive or email
documentationfeedback@microstrategy.com.
• Help: In general, MicroStrategy help provides:
Detailed steps to perform procedures
Descriptions of each option on every software screen
Due to translation time, manuals in languages other than English may
contain information that is one or more releases behind. You can see
the version number on the title page of each manual. For the most
up-to-date translations, refer to the MicroStrategy Knowledge Base.
37
Preface
MicroStrategy Functions Reference
xx
Resources
© 2011 MicroStrategy, Inc.
MicroStrategy overview and evaluation
• Introduction to MicroStrategy: Evaluation Guide
Instructions for installing, configuring, and using the MicroStrategy
Evaluation Edition of the software. This guide also includes a detailed,
step-by-step evaluation process of MicroStrategy features, where you
perform reporting with the MicroStrategy Tutorial project and its sample
business data.
• MicroStrategy Evaluation Edition Quick Start Guide
Overview of the installation and evaluation process, and additional
resources.
• Evaluate MicroStrategy for Linux Guide: In a Windows or Linux
Environment with the MicroStrategy Evaluation Edition Virtual
Appliance
Evaluate MicroStrategy for Linux, in a Microsoft Windows or Linux
environment, with the MicroStrategy Evaluation Edition Virtual
Appliance. This guide provides all details to download, activate, and
evaluate MicroStrategy software running in a Linux environment.
• MicroStrategy Reporting Suite: Quick Start Guide
Evaluate MicroStrategy as a departmental solution. Provides detailed
information to download, install, configure, and use the MicroStrategy
Reporting Suite.
• MicroStrategy Mobile Suite: Quick Start Guide
Evaluate MicroStrategy Mobile as a departmental solution. Provides
detailed information to download, install, configure, and use the
MicroStrategy Mobile Suite.
Manuals for query, reporting, and analysis
• MicroStrategy Installation and Configuration Guide
Information to install and configure MicroStrategy products on
Windows, UNIX, Linux, and HP platforms, as well as basic maintenance
guidelines.
• MicroStrategy Upgrade Guide
Instructions to upgrade existing MicroStrategy products.
Documents you may be interested
Documents you may be interested
