Top 3 Underlyings per Country
You may know the TopCount
function, which is very helpful when you want to keep only the members with the highest values. Now imagine you have in your cube the dimensions Underlyings and Geography and you want to retrieve for each region the 3 underlyings that have the highest PnL.
You cannot do this only with TopCount
, you will have to use Generate
. This functions acts like a For loop.
Here is the mdx that will do what we want:
SELECT NON EMPTY
Generate
(
[Geography].[Region].MEMBERS,
TopCount
(
{[Geography].CurrentMember} * [Underlying].[ALL].[AllMember].Children,
3,
[Measures].[TotalPnl.SUM]
)
) ON ROWS,
{[Measures].[TotalPnl.SUM]} ON COLUMNS
FROM [PnlCube]
Here is how we can add the total per region:
SELECT NON EMPTY
Generate
(
[Geography].[Region].MEMBERS,
{
{([Geography].CurrentMember, [Underlying].[ALL].[AllMember])},
TopCount
(
{[Geography].CurrentMember} * [Underlying].[ALL].[AllMember].Children,
3,
[Measures].[TotalPnl.SUM]
)
}
) ON ROWS,
{[Measures].[TotalPnl.SUM]} ON COLUMNS
FROM [PnlCube]
Books that represent 80% of VAR
The TopCount
function returns the specified number of elements with the highest values. But in some cases you may want to retrieve the members with the highest values that contribute to x% of the grand total.
There is an Mdx function for this: TopPercent
.
The following query will retrieve the books with the highest VaR that contribute to 80% of the VaR of all the books.
SELECT {
{[Book].[ALL].[AllMember]},
TopPercent
(
[Book].[ALL].[AllMember].Children,
80,
[Measures].[historical.VaR]
)
} ON ROWS
FROM [VarCube]
WHERE [Measures].[historical.VaR]
Daily Turnover Change
With MDX functions like PrevMember
and NextMember
you can write formulas that compare the measure values between 2 days/ weeks/ months/ years.
For instance:
(
[Measures].[Turnover.SUM]/
([Measures].[Turnover.SUM], [Time].CurrentMember.PrevMember)
 1
)
* 100
[Measures].[Turnover.SUM]
has the same value as ([Measures].[Turnover.SUM], [Time].CurrentMember)
which is the turnover for the current member on the time dimension.
([Measures].[Turnover.SUM], [Time].CurrentMember.PrevMember)
is the turnover for the member that is before the current member on the time dimension. The expression returns the relative growth between the 2 members.
If you put [Time].[Day].Members
on one of the axes, you will have the daily turnover change. If you put [Time].[Month].Members
on one of the axes, you will have the monthly turnover change.
The final Mdx query will look like this:
WITH MEMBER [Measures].[Daily Turnover Change] AS
IIF
(
[Time].CurrentMember.PrevMember
IS NULL,
NULL,
(
[Measures].[Turnover.SUM]/
([Measures].[Turnover.SUM], [Time].CurrentMember.PrevMember)
1
)
* 100
)
SELECT NON EMPTY
[Time].[Day].MEMBERS ON ROWS
FROM [Amazon]
WHERE [Measures].[Daily Turnover Change]
The IIf is used to handle the value of the formula for the first day. [Time].CurrentMember.PrevMember
does not exist for the first day; it will be null
.
How to compute day to day differences
This section explains how to use MDX calculated members to compute differences between adjacent members. A typical example would be the evolution of delta or pl from one business date to the next.
The new MDX engine allows this to be computed using MDX as an alternative to the traditional post processed approach. The example here can be deployed on the sandbox project by cut and paste of the following MDX into ActiveUI's query editor.
This screen shot shows an MDX calculated member called DIFF which is evaluated as the difference in delta.SUM from one member in the dates dimension to the next.
The important thing to focus on is the definition in the MDX statement of a calculated member (the WITH MEMBER
clause) called DIFF. It is computed as the difference between the current value and the previous value and as you can see from the screen shot – it is generic in that it works not only for T against T1 but also at T1 against T2 and will work across all pairs of members on the date dimension.
Another nice feature to notice is the FORMAT
clause in the the query – the screen shot shows how ActivePivot respects this formatting directive – small point but very important for a readable presentation of the data.
WITH MEMBER [Measures].[DIFF] AS
IIF
(
[HistoricalDates].CurrentMember.PrevMember
IS NULL,
NULL,
[Measures].[delta.SUM]

(
[Measures].[delta.SUM],
[HistoricalDates].CurrentMember.PrevMember
)
),
FORMAT = "#,###.00"
SELECT
NON EMPTY
{
[Measures].[DIFF],
[Measures].[delta.SUM]
}
ON ROWS,
NON EMPTY
Hierarchize
(
{[HistoricalDates].[AsOfDate].MEMBERS}
)
ON COLUMNS
FROM [EquityDerivativesCube]
Measure as mathematical formula with formatting
Measures can be added with a mathematical formula built with operators and operands (scalars or other measures).
For instance:
/* Full VaR = Square root of sum of Square of historical VaR and Square of stress VaR */
in other words:
fullVaR = (historicalVaR^2 + stressVaR^2) ^ (1/2)
Also we want to format the result using a 2 decimals precision and thousands separator, such as 1,123,456.89
The Mdx query will look like this:
WITH /* full valueatrisk combining historical and stress values */
MEMBER [Measures].[fullVaR] AS
([Measures].[historical.VaR] ^ 2 + [Measures].[stress.VaR] ^ 2) ^ 0.5,
FORMAT = '#,###.##'
SELECT
NON EMPTY
Hierarchize
(
{DrillDownLevel({[ProfitCenter].[ALL].[AllMember]})}
)
ON ROWS,
NON EMPTY
{
[Measures].[historical.VaR],
[Measures].[stress.VaR],
[Measures].[fullVaR]
}
ON COLUMNS
FROM [VarCube]
Filtering on a measure values
We can filter members based on a set of conditions on a measures values with the IIF
function and operators such as AND
, OR
, >
, <
etc...
For instance:
/* Select all Products that have a contributor count between 50 and 1000 and all Products with a count above 1000on a separate column */
The MDX query will look like this:
WITH
MEMBER [Measures].[LargeCount] AS
IIF
(
[Measures].[contributors.COUNT] <= 1000
AND
[Measures].[contributors.COUNT] >= 50,
[Measures].[contributors.COUNT],
NULL
)
MEMBER [Measures].[VeryLargeCount] AS
IIF
(
[Measures].[contributors.COUNT] > 1000,
[Measures].[contributors.COUNT],
NULL
)
SELECT
NON EMPTY
Hierarchize
(
{DrillDownLevel({[Product].[ALL].[AllMember]})}
)
ON ROWS,
{
[Measures].[contributors.COUNT],
[Measures].[LargeCount],
[Measures].[VeryLargeCount]
}
ON COLUMNS
FROM [VarCube]