MDX case statements in WHERE clause

Question

I'm playing around with MDX in the Adventure Works. I trying different things to just practice (so I know there's probably a better way to use the date hierachy to achieve this, but I'm trying it with the Name of Month as opposed to the specific month and year names (if that makes sense). What I'm trying to do is use a case statement that would lag for 2 of my sets, but no lag for the other 2. In other words, I want it to return the 2010 values for my Q1 (July,Aug,Sept) and Q2 (Oct, Nov, Dec) sets, but 2011 values for the Q3(Jan,Feb,Mar) and Q4 sets (Apr, May, June). Here's what I have, but the case statement just gives lag 1 for all. So I'm understanding how it's working, but can't seem to wrap my head around how to return the different values for each set as stated above.

WITH 
    SET [Q1 Combined] AS {
    [Date].[Month of Year].&[7],
    [Date].[Month of Year].&[8],
    [Date].[Month of Year].&[9]   }

SET [Q2 Combined] AS {
    [Date].[Month of Year].&[10],
    [Date].[Month of Year].&[11],
    [Date].[Month of Year].&[12]   }

SET [Q3 Combined] AS {
    [Date].[Month of Year].&[1],
    [Date].[Month of Year].&[2],
    [Date].[Month of Year].&[3]   }

SET [Q4 Combined] AS {
    [Date].[Month of Year].&[4],
    [Date].[Month of Year].&[5],
    [Date].[Month of Year].&[6]   }

MEMBER [Date].[Month of Year].[FY Q1 Fix] AS Aggregate([Q1 Combined])
MEMBER [Date].[Month of Year].[FY Q2 Fix] AS Aggregate([Q2 Combined])
MEMBER [Date].[Month of Year].[FY Q3 Fix] AS Aggregate([Q3 Combined])
MEMBER [Date].[Month of Year].[FY Q4 Fix] AS Aggregate([Q4 Combined])

SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,

{
[Date].[Month of Year].[FY Q1 Fix],
[Date].[Month of Year].[FY Q2 Fix],
[Date].[Month of Year].[FY Q3 Fix],
[Date].[Month of Year].[FY Q4 Fix]} ON ROWS
                                                                                            } ON ROWS


FROM [Adventure Works]

WHERE **I WANT TWO DIFFERENT SLICES**

So in other words, I want:

[FY Q1 Fix] and [FY Q2 Fix] to be sliced and show [Date].[2010] measures

[FY Q3 Fix] and [FY Q4 Fix] sliced by and show [Date].[2011] measures

Answer 1

I'd be more inclined to put the logic of the different slices in your WITH clause rather than WHERE clause. So in the following you can see there is one hard-coded year and then I use lag against two of the quarter sets:

WITH 
  SET [targetYear] AS 
    [Date].[Fiscal Year].&[2008] 
  SET [Q1 Combined] AS 
      {
        [Date].[Month of Year].&[7]
       ,[Date].[Month of Year].&[8]
       ,[Date].[Month of Year].&[9]
      }
    * 
      [targetYear].Item(0) 
  SET [Q2 Combined] AS 
      {
        [Date].[Month of Year].&[10]
       ,[Date].[Month of Year].&[11]
       ,[Date].[Month of Year].&[12]
      }
    * 
      [targetYear].Item(0) 
  SET [Q3 Combined] AS 
      {
        [Date].[Month of Year].&[1]
       ,[Date].[Month of Year].&[2]
       ,[Date].[Month of Year].&[3]
      }
    * 
      [targetYear].Item(0).Lag(1) 
  SET [Q4 Combined] AS 
      {
        [Date].[Month of Year].&[4]
       ,[Date].[Month of Year].&[5]
       ,[Date].[Month of Year].&[6]
      }
    * 
      [targetYear].Item(0).Lag(1) 
  MEMBER [Date].[Month of Year].[FY Q1 Fix] AS 
    Aggregate([Q1 Combined]) 
  MEMBER [Date].[Month of Year].[FY Q2 Fix] AS 
    Aggregate([Q2 Combined]) 
  MEMBER [Date].[Month of Year].[FY Q3 Fix] AS 
    Aggregate([Q3 Combined]) 
  MEMBER [Date].[Month of Year].[FY Q4 Fix] AS 
    Aggregate([Q4 Combined]) 
SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS
 ,{
    [Date].[Month of Year].[FY Q1 Fix]
   ,[Date].[Month of Year].[FY Q2 Fix]
   ,[Date].[Month of Year].[FY Q3 Fix]
   ,[Date].[Month of Year].[FY Q4 Fix]
  } ON ROWS
FROM [Adventure Works];