Search code examples
ssasgroupingmdx

PARALLELPERIOD error in MDX Query

I have used the below mentioned query for a PARALLAELPERIOD

WITH 
  MEMBER [MEASURES].[DATO AR] AS 
    IIF
    (
      [Measures].[Dato] = 'AT'
     ,NULL
     ,[Measures].[Dato]
    ) 
  MEMBER [MEASURES].[Indice] AS 
    IIF
    (
      ([Tempo].[Mese Anno].[Dic-2011],[Measures].[Dato]) = 0
     ,null
     ,
        [Measures].[DATO AR] / ([Tempo].[Mese Anno].[Dic-2011],[Measures].[Dato])
      * 100
    ) 
SELECT 
  NON EMPTY 
    {
      [MEASURES].[DATO AR]
     ,[MEASURES].[Indice]
    } ON 0
 ,ParallelPeriod
  (
    [Tempo].[Mese Anno].CurrentMember.UniqueName
   ,3
   ,Filter
    (
      Order
      (
        NonEmpty([Tempo].[Periodo].[Mese Anno])
       ,[Tempo].[Periodo].CurrentMember.MemberValue
       ,Bdesc
      )
     ,Instr
      (
        [Tempo].[Periodo].[Mese Anno].CurrentMember.Name
       ,'Dic'
      )
    )
  ) ON 1
FROM AGRO;

I am getting an error as below:

The PARALLELPERIOD function expects a member expression for the 3 argument. A tuple set expression was used.

Below image show the dimension being used:

enter image description here

The data available is until April 2016. I want to get only the last three December value, i.e., Dic-2015, Dic-2014 and Dic-2013

And when we reach Decmeber 2016, it should be Dic-2016, Dic-2015 and Dic-2014.

Can someone suggest the change in the query ,mentioned above.

Solution

  • In AdvWrks I can do this - using your nifty little Filter idea:

    SELECT 
  {} ON 0
 ,Filter
  (
    [Date].[Calendar].[Month]
   ,Instr
    (
      [Date].[Calendar].CurrentMember.Name
     ,'December'
    )
  ) ON 1
FROM [Adventure Works];

    This returns the set of every December

    enter image description here

    So we can now just use Tail to get the last 3 of them:

    SELECT 
  {} ON 0
 ,Tail
  (
    Filter
    (
      [Date].[Calendar].[Month]
     ,Instr
      (
        [Date].[Calendar].CurrentMember.Name
       ,'December'
      )
    )
   ,3
  ) ON 1
FROM [Adventure Works];

    The above returns this:

    enter image description here

    If I only want the last 3 with data best to use the NonEmpty function:

    SELECT 
  {} ON 0
 ,Tail
  (
    Filter
    (
      NonEmpty([Date].[Calendar].[Month])
     ,Instr
      (
        [Date].[Calendar].CurrentMember.Name
       ,'December'
      )
    )
   ,3
  ) ON 1
FROM [Adventure Works];

    This changes the 3 Decembers to the following:

    enter image description here

    To make things more readable I prefer this sort of logic in a WITH clause.