MDX: Mixed row aggregation types within single period column time aggregation

Question

I am trying wrap my head around a way to produce the following result from a Mondrian cube.

Sample Values:

Year  Month  Sales
----  -----  -----
2015  Jan     10 
2015  Feb     11 
2015  Mar     12 
2015  Apr     10 
2015  May     11 
2015  Jun     12 


                        Jan-Mar 2015 | Apr-Jun 2015
---------------------------------------------------
Sales Sum              |      33     |     33
Sales Average          |      11     |     11

The current MDX is something like this:

with
     member [Date].[JAN-MAR] as Aggregate([Date].[2015].[3].lag(2):[Date].[2015].[3])
     member [Date].[APR-JUN] as Aggregate([Date].[2015].[6].lag(2):[Date].[2015].[6])
     member [Measures].[Sales Sum] as Sum([Date].CurrentMember, [Measures].[Sales])
     member [Measures].[Sales Average] as Avg([Date].CurrentMember, [Measures].[Sales])
select
     {[Date].[JAN-MAR],
      [Date].[APR-JUN]} on columns,
     {[Measures].[Sales Sum],
      [Measures].[Sales Average]} on rows
from [Cube] 

The question is how can I get a row to specify an aggregate to use for the current column period aggregation?

Update (17 Aug 2018)

I think I have found a solution, before I get into that I think I should give more background into the scenario. We are using Mondrian to provide some financial reports. Due to the complexity of the reports combined with the fact that end users must be able to create them we have created our own mini reporting tool.

One of the most common report types is measures on rows and columns with various date aggregations e.g. Three Month Rolling Average / Financial Year to Date etc all based on a report parameter date selection offset.

The complexity comes in where for the same column they want different rows to aggregate differently. An example would be the Financial Year to Date column, some rows measures must be summed, some must be averaged and some must return the closing balance.

I haven't found an easy want to model this in the cube yet :/

However I found a way to get it to work by mistake that seems relevantly robust and is also fast. As it turns out Mondrian does not validate member attributes, i.e. you can declare and reference whatever member attributes you want. This has turned out to provide an easy way to can get access to the correct date slice and perform whatever aggregate I want e.g:

with
     member [Date].[JAN-MAR] as Aggregate([Date].[2015].[3].lag(2):[Date].[2015].[3]), START_MONTH_MEMBER='[Date].[2015].[1]', END_MONTH_MEMBER='[Date].[2015].[3]'
     member [Date].[APR-JUN] as Aggregate([Date].[2015].[6].lag(2):[Date].[2015].[6]), START_MONTH_MEMBER='[Date].[2015].[4]', END_MONTH_MEMBER='[Date].[2015].[6]'
     member [Measures].[Sales Sum] as Sum([Date].CurrentMember, [Measures].[Sales])
     member [Measures].[Sales Average] as Avg(StrToMember([Date].CurrentMember.Properties('START_MONTH_MEMBER')):StrToMember([Date].CurrentMember.Properties('END_MONTH_MEMBER')), [Measures].[Sales])
select
     {[Date].[JAN-MAR],
      [Date].[APR-JUN]} on columns,
     {[Measures].[Sales Sum],
      [Measures].[Sales Average]} on rows
from [Cube] 

So far this works well. One thing that doesn't work is I cannot get StrToSet to work. In theory you should be able to declare a set in the with member property and then use the in the measure.

StrToMember(([Date].CurrentMember.Properties('MONTH_RANGE_SET'))

So this what I have working for now, would love some feedback on that?

Answer 1

This is a bit time consuming, but should work:

with
     member [Date].[JAN-MAR] as Aggregate([Date].[2015].[3].lag(2):[Date].[2015].[3])
     member [Date].[APR-JUN] as Aggregate([Date].[2015].[6].lag(2):[Date].[2015].[6])        
     member [Measures].[Sales Sum] as Sum([Date].CurrentMember, [Measures].[Sales])
     member [measures].yearvalues as [Date].currentmember.member_value
     member [Measures].[Sales Average] as 
     AVG
       (
        StrToSet(
                    "[Date].[2015].&["           + 
                    CASE
                         LEFT(measures.yearvalues, 3) 
                         WHEN "JAN" THEN 1 
                         WHEN "APR" THEN 4 END           + 
                    "]:[Date].[2015].&["         + 
                    CASE
                         RIGHT(measures.yearvalues, 3) 
                         WHEN "MAR" THEN 3
                         WHEN "JUN" THEN 5 END           +
                    "]"
                )
        ,
        [Measures].[Sales]
      ),
    format_string = "#.##"
select
     {[Date].[JAN-MAR],
      [Date].[APR-JUN]} on columns
     {[Measures].[Sales Sum],
      [Measures].[Sales Average]} on columns
from [Cube] 

Answer 2

Far from ideal but best I can do at the moment:

WITH 
  SET [JAN-MAR] AS 
      [Date].[Calendar].[Month].&[2006]&[3].Lag(2)
    : 
      [Date].[Calendar].[Month].&[2006]&[3] 
  SET [APR-JUN] AS 
      [Date].[Calendar].[Month].&[2006]&[6].Lag(2)
    : 
      [Date].[Calendar].[Month].&[2006]&[6] 
  MEMBER [Date].[Calendar].[JAN-MAR] AS 
    Aggregate([JAN-MAR]) 
  MEMBER [Date].[Calendar].[APR-JUN] AS 
    Aggregate([APR-JUN]) 
  MEMBER [Measures].[Sales Sum] AS 
    [Measures].[Internet Sales Amount] 
  MEMBER [Measures].[Sales Average] AS 
    [Measures].[Internet Sales Amount] / [JAN-MAR].Count 
SELECT 
  {
    [Date].[Calendar].[JAN-MAR]
   ,[Date].[Calendar].[APR-JUN]
  } ON 0
 ,{
    [Measures].[Sales Sum]
   ,[Measures].[Sales Average]
  } ON 1
FROM [Adventure Works];

So I thought maybe I'd try adding the custom members to an unrelated dimension (effectively make it a utility dimension). This works ok but extracting the count of number of related months is still proving difficult. This is the current effort:

WITH 
  SET [JAN-MAR] AS 
      [Date].[Calendar].[Month].&[2006]&[3].Lag(2)
    : 
      [Date].[Calendar].[Month].&[2006]&[3] 
  SET [APR-JUN] AS 
      [Date].[Calendar].[Month].&[2006]&[6].Lag(2)
    : 
      [Date].[Calendar].[Month].&[2006]&[6] 
  MEMBER [Product].[Category].[JAN-MAR] AS 
    Aggregate
    (
      [JAN-MAR]
     ,[Product].[Category].[All Products]
    ) 
  MEMBER [Product].[Category].[APR-JUN] AS 
    Aggregate
    (
      [APR-JUN]
     ,[Product].[Category].[All Products]
    ) 
  MEMBER [Measures].[Sales Sum] AS 
    [Measures].[Internet Sales Amount] 
  MEMBER [Measures].[Sales Avg] AS 
      [Measures].[Internet Sales Amount]
    / 
      NonEmpty
      (
        [Date].[Calendar].[Month].MEMBERS
       ,(
          [Product].[Category].CurrentMember
         ,[Measures].[Internet Sales Amount]
        )
      ).Count //<<<<currently returning 72 rather than 3
SELECT 
  {
    [Product].[Category].[JAN-MAR]
   ,[Product].[Category].[APR-JUN]
  } ON 0
 ,{
    [Measures].[Sales Sum]
   ,[Measures].[Sales Avg]
  } ON 1
FROM [Adventure Works];

We can see that it is getting divided by 72 rather than 3:

---

Problem as I currently see it is trying to get hold of the number of related months to each of the custom members after they have been aggregated - here is a simplified example of what I mean:

WITH 
  SET [JAN-MAR] AS 
      //<< set of 3 months
      [Date].[Calendar].[Month].&[2006]&[1]
    : 
      [Date].[Calendar].[Month].&[2006]&[3] 
  MEMBER [Product].[Category].[JAN-MAR] AS 
    //<< chuck on unconnected hierarchy
    Aggregate
    (
      [JAN-MAR]
     ,[Product].[Category].[All Products]
    ) 
  MEMBER [Measures].[countMonthsRelatedToMember] AS  //<<attempt to count mths related to [Product].[Category].[JAN-MAR]    
    NonEmpty
    (
      [Date].[Calendar].[Month].MEMBERS
     ,(
        [Product].[Category].CurrentMember
       ,[Measures].[Internet Sales Amount]
      )
    ).Count //<<<<currently returning 72 rather than 3
SELECT 
  [Product].[Category].[JAN-MAR] ON 0
 ,[Measures].[countMonthsRelatedToMember] ON 1
FROM [Adventure Works];