ABC Classification

I was asked to make a simple ABC classification for a sales measures. I combined exists with TopPercent to identify:

• Class A = between the top 0 and 20 %
• Class B = between the top 20 and 80%
• Class C = between the top 80 and 100%

Calculating an Age

One of the questions you often get is how to calculate a customers age on a certain date. Luckily Chris Webb published a great example: http://cwebbbi.wordpress.com/2014/06/09/calculating-ages-in-mdx/.

I made a small extension to this by making it level aware, this means you can also use it at year, semester, quarter and month level.

To make it level aware I use the .Level.Ordinal extension on the Currentmember. This returns the Level of the member within the Hierarchy (0 = All, 1 = Year, 2 = Semester, 3 = Quarter, 4 = Month, 5 = date).

CASE WHEN and NULL / 0 (zero)

When you have a CASE WHEN in MDX you basically have two options:

The Simple or Searched statement:

CASE [<<Set To Be Evaluated>>]

WHEN [<<condition1>>] THEN [<<action>>]

WHEN [<<condition2>>] THEN [<<action>>]

ELSE [<<action>>] END

Calculating Median and Percentile

MEDIAN or 50th Percentile

According to Wikipedia the median or 50th percentile is the numerical value separating the higher half of a data sample, a population, or a probability distribution, from the lower half. Some examples:

Set	Median / 50th percentile
1,2,3,4,5	3
1,2,3,3,4,5	3
1,2,3,4,4,5	3,5
1,2,3,3,4,4,5	3
1,2,3,3,4,4,5,5	3,5

In this example you see that if the set contains an even number of members the median is the average between the numbers left and right from the centre.

YearToDate (YTD) Previous Year (also QTD, MTD, WTD)

This is simple is you combine ParallelPeriod with the YTD functionality:

WITH
MEMBER [Measures].[Sales Amount YearToDate]
AS
    SUM(YTD( [Date].[Calendar].CurrentMember),[Measures].[Sales Amount])

MEMBER [Measures].[Sales Amount Previous Year]
AS
    SUM(ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember),[Measures].[Sales Amount])

MEMBER [Measures].[Sales Amount YearToDate Previous Year]
AS
    SUM(YTD( ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember)),[Measures].[Sales Amount])

PeriodsToDate, YearToDate (YTD), QuarterToDate (QTD), MonthToDate (MTD), WeekToDate (WTD)

PeriodsToDate, YearToDate (YTD), QuarterToDate (QTD), MonthToDate (MTD), WeekToDate (WTD) are all MDX functions which aggregate date from de first date of a chosen period up and an including a given end date.

Percentage difference from previous parallel period

Let’s start by getting the parallel period:

WITH MEMBER [Measures].[Sales Amount PY]
AS
(
    [Measures].[Sales Amount]
    , ParallelPeriod
        ([Date].[Calendar].[Calendar Year] // Level
        , 1 // number of periods back
        , [Date].[Calendar].CurrentMember) // Start member
)
, format_string = "Currency"

ParallelPeriod

The ParallelPeriod functionality is closely related to the Cousin functionality. The main difference is that the ParallelPeriod function expects a hierarchy of the type Time.
It will get you the value of a measure based given top-level.

Percentage of one level up

One of the most common calculations is calculating the percentage of the current value to the total one level up. For instance [Measures].[Sales Amount] for the quarter compared to the semester.
This gives use several challenge's.
1. What is the total amount one level up. With the .CurrentMember property we know where we are. If we combine this with the .Parent property we can calculate the total amount: ([Measures].[Sales Amount],[Date].[Calendar].CurrentMember.Parent) .

RANGE

The Range functionality allows you to define a set of member based on the first and last member of the range. You do this by using the colon “:”.
Fixed Range:
SELECT
[Date].[Calendar Year].[CY 2011]:[Date].[Calendar Year].[CY 2014] // FIXED RANGE
ON COLUMNS,
[Measures].[Sales Amount] ON ROWS
FROM [Adventure Works]

UNION

The UNION functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).
UNION will give all the members of SET1,SET2…. SETn. By default it will remove any duplicates.

Intersect

The Intersect functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).
Intersect will give all the members of SET1 which are also in SET2. By default it will remove any duplicates.