3

MDX has a nice feature whereby I can specify a range of members:

SELECT ([Canada],[2006]:[2011]) on Rows,
      [Measures].members on Columns
FROM [Sales]

Is there a way to calculate the set of even years: {[2006], [2008], [2010]}? I am looking for a way that would work for large sets, so that listing the years manually is not an option.

MoazRub
  • 2,881
  • 2
  • 10
  • 20
AlwaysLearning
  • 7,257
  • 4
  • 33
  • 68

3 Answers3

2

You can filter you function using a filter function, a declared function and MOD function (MOD returns the remainder from the division - like % in Java ) : 

WITH
  FUNCTION isEven(Value _number) as Mod( Int(_number) , 2 ) = 0
SELECT
  FILTER( [Date].[Date].[Year] as t, isEven( Year( t.current.key) ) )  on 0
FROM [Cube]

If you are using this filter often you could create a FilterEven declared function once in the script (same for isEven() )

ic3
  • 7,917
  • 14
  • 67
  • 115
  • This does not work in icCube (the sample Sales cube). `Year()` results in NULL exception. – AlwaysLearning Nov 18 '18 at 17:02
  • I think for this cube the year key is a string not a date, change Year() function to CLong() or if the key is already an integer to nothing. https://www.iccube.com/support/documentation/mdx/Excel%20Conversion%20Functions.php – ic3 Nov 19 '18 at 08:30
1

Try this. I used adventure works for the query.For the mod logic i took help from Mod Logic 

WITH 
MEMBER [Measures].[Data Type] AS 
[Date].[Day of Year].CurrentMember.Properties ("Member_Value",TYPED) 

MEMBER [Measures].[IsEven] as
[Measures].[Data Type]-Int([Measures].[Data Type]/2)*2 

select {[Measures].[Internet Order Count]  }
on columns,
filter (
[Date].[Day of Year].[Day of Year],
[Measures].[IsEven]=0)
 on rows 
from [Adventure Works]
MoazRub
  • 2,881
  • 2
  • 10
  • 20
0

Plus you can have a column in the date dimension have 1,0 to indicate if the year is even or odd. Then simply use that column in the MDX query , no need to do all the above manipulations

MoazRub
  • 2,881
  • 2
  • 10
  • 20