MDX query to compare different years

2
votes

I am new to MDX and wondered if it is possible to create a query that shows a Sales amount per Year and per Month on two different axes, even if the sales date is a single dimension.

Something like:

Sales    |   2010    |    2011    |   Diff   
---------+-----------+------------+----------
Jan      |  1234,00  |  2345,10   |  +80%
Feb      |    ...
...

EDIT: Added mondrian to tags, because there seem to be possibilities with other MDX implementations not available in mondrian.

2
mdxmondrian

2 Answers

3
votes

Yes the solution is around calculated members :

Let's imagine your initial MDX looks like :

Select
 { [Calendar].[Year].[2010],[Calendar].[Year].[2011] } on 0,
 { [Calendar].[Months].members } on 1
from [Cube]

You can add a calculated member in the [Year] hierarchy :

With
 Member [Calendar].[Year].[Diff] as [Calendar].[Year].[2011] / [Calendar].[Year].[2010]-1, FORMAT_STRING='percent'
Select
 { [Calendar].[Year].[2010],[Calendar].[Year].[2011], [Calendar].[Year].[Diff] } on 0,
 { [Calendar].[Months].members } on 1
from [Cube]

You can also add a more elegant and flexible solution, by using utility or statistical dimensions. Those dimension instead of holding data define transformations / functions and the output will not be exactly the one you're looking, but it's an interesting concept.

1
votes

The problem can be solved with the ParallelPeriod function:

WITH MEMBER [Measures].[Einheiten Vorjahr] 
  AS '(ParallelPeriod([Year],1),
      [Measures].[quantity])'
SELECT {[Measures].[quantity],[Measures].[Einheiten Vorjahr]} ON COLUMNS,
       [date].[2010].children on rows
FROM salesorderitems