Combine 2 mdx queries

Question

I have 2 MDX queries from the same cube. Both use the same measure but with different time sets (both the time sets are same dimension but different hierarchies).

I want to join them in the same table results, so it will present the 2 measures (cut by the different sets) and another time dimension (“Day of Week”) that also uses the same time dimension. The queries can be run separately as follows:

   with member [Measures].[AVG_6_WEEKS] as 
     [Measures].[Number of Answered Comms] /6
    select 
    nonempty([Comm Date UTC].[Day of Week].children)
    on 0, 
    [Measures].[AVG_6_WEEKS]
    on 1 
    from (select {LASTPERIODS( 42,[Comm Date UTC].[Year Month Day].lastsibling.lastchild.lastchild.lastchild.prevmember )}
    on 0 from comms)

;
 with member [Measures].[Answered Comms] as 
 [Measures].[Number of Answered Comms] 
select 
nonempty([Comm Date UTC].[Day of Week].children)
on 0, 
[Measures].[Answered Comms]
on 1 
from (select {LASTPERIODS( 7,[Comm Date UTC].[Year Month 
Day].lastsibling.lastchild.lastchild.lastchild.prevmember )}
    on 0 from comms)

Can it be done? I always get an error that I can’t use the same time hierarchies in the query… Any idea? Something like SQL were I can join 2 views?

Thank you

Yoni.

score 0 · Answer 1 · answered Nov 04 '14 at 13:09

The following MDX should do that:

with member [Measures].[AVG_6_WEEKS] as 
     Aggregate({LASTPERIODS( 42,[Comm Date UTC].[Year Month Day].lastsibling.lastchild.lastchild.lastchild.prevmember )},
               [Measures].[Number of Answered Comms] /6
              )
     member [Measures].[Answered Comms] as 
     Aggregate({LASTPERIODS( 7,[Comm Date UTC].[Year Month Day].lastsibling.lastchild.lastchild.lastchild.prevmember )},
               [Measures].[Number of Answered Comms]
              ) 
select 
nonempty([Comm Date UTC].[Day of Week].children)
on 0, 
{ [Measures].[AVG_6_WEEKS], [Measures].[Answered Comms] }
on 1 
from comms

I moved the context from the subselects in your queries to the member definition using the Aggregate function.

thank you Frank, but using this script causes an error in the result table: Aggregated functions cannot be used on calculated members in the measures dimension. — Yoni, Nov 06 '14 at 06:31

Combine 2 mdx queries

1 Answers1