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]

generate top count

go to top

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]

Top per cent

go to top

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]

previous member

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.

go to top

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 T-1 but also at T-1 against T-2 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]

go to top

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 value-at-risk 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]

MDX formula

go to top

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]

Filtering on measure