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.

Except

The Except functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).

Except will give all the members of SET1 which are not in SET2. By default it will remove any duplicates which are in SET1.

Cousin

The Cousin functionality will get you the member on same position as the starting based on a new toplevel member. Most of the time you use it in the same way you use ParallelPeriod.

If you look at a date or calendar dimension is might look like this: All ==> Year ==> Semester ==> Quarter ==> Month ==> Date.

If you use a query like this:

Descendants

The Descendants functionality although in name de reverse of the Ascendants functionality has a lot more options.

Before you read on just a reminder the Geography hierarchy in the Adventure Works sample is: All ==> Country ==> State-Province ==> City ==> Postal Code.

Let’s start with the base:

SELECT Descendants
   (
    [Geography].[Geography].[Country].&[United States]
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

Ascendants

The Ascendants functionality gives back all levels from and including the current level.
SELECT
   Measures.[Sales Amount] ON COLUMNS,
   Order(
      Ascendants(
      [Product].[Product Categories].[Product].&[448]
      ),
      DESC
   ) ON ROWS
FROM
   [Adventure Works]
where ([Date].[Calendar Year].&[2012])

Ancestor

The Ancestor functionality allows us to find, parents, grand parents, great grand parents etc.… The number of levels you want to go up can be a number or a dedicated level.
The Ancestor with a level of 1 is the same as the .Parent functionality
Let’s try:

AllMembers vs Children

Both do what their name implies .
AllMembers will return All Members on the current level and all levels below.
SELECT
    {       
        ([Date].[Calendar Year].&[2013], [Measures].[Sales Amount])
    }
ON COLUMNS
,
    {
        ([Product].[Product Model Lines].AllMembers)
    } ON ROWS
FROM
   [Adventure Works]

Parent, FirstChild and LastChild

Using the Parent functionality you can get the First Child and Last Child within hierarchy level. 15-JAN-2011 ==> Parent = JAN-2011 ==> FirstChild = 1-JAN-2011 ==> LastChild = 31-JAN-2011.
Here is an example:

LAG an LEAD

The LAG and LEAD functionality allow to jump back or forward a number of step on the same hierarchy level.
LAG(1) and LEAD(1) is the same as PrevMember and Next Member.