Wednesday, September 2, 2015
Friday, August 29, 2014
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%
Friday, August 22, 2014
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).
Sunday, August 17, 2014
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
Sunday, August 10, 2014
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|
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.
Wednesday, August 6, 2014
MEMBER [Measures].[Sales Amount YearToDate]
SUM(YTD( [Date].[Calendar].CurrentMember),[Measures].[Sales Amount])
MEMBER [Measures].[Sales Amount Previous Year]
SUM(ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember),[Measures].[Sales Amount])
MEMBER [Measures].[Sales Amount YearToDate Previous Year]
SUM(YTD( ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember)),[Measures].[Sales Amount])
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.