none
Combining measures over different dimension values RRS feed

  • Question

  • Hi!
     
    I didn't find any similar problem on the forum posted because I even don't know what exactly to look for.

    I have a cube with some financial data. There is measure named Flow which I am interested in. Among other dimensions which are not imporant right now there are Time and Version dimensions. Version dimension is for keeping Realization, Plan and other versions of financial figures. I can write a query to display Realization or Plan in any time period.

    The problem I have is that I don't know how to write a query when I want to combine these two versions. For instance let's say we have Realization until December 2008. I want to get combined figures for the period from October 2008 until March 2009. That means measure Flow should aggregate figures from October up to december 2008 in Realization version and from January 2009 to March 2009 Plan version. I can get two Flow columns with Realization and Plan values separately, but I don't know how to get them summed together in one column.

    I am using calculated members for Time to get Realization and Plan period like this.

    MEMBER [Time].[TimeRe] as 'Aggregate ({ ([Time].&[200811] : [Time].&[200821]) })'

    MEMBER [Time].[TimePl] as 'Aggregate ({ ([Time].&[200822] : [Time].&[200823]) })'

    Then I am using tuples to get Flow measure like this

    ([Measures].[Flow], [Account].&[key2], [Unit].[EUR], [CostType].[All], [Version].&[Re], [Time].[TimeRe]),
    ([Measures].[Flow], [Account].&[key2], [Unit].[EUR], [CostType].[All], [Version].&[Pl], [Time].[TimePl]),

    These line produce one column each and I don't know how to get them in one column. I tried "+" between those 2 tuples but it says

    "The function expects a tuple set expression for the 2 argument. A string or numeric expression was used."

    Thanks, for answers.

    Sunday, February 1, 2009 9:46 PM

Answers

  • You could crossjoin your time periods with the versions and then you should be able to union the sets with the + operator before aggregating them

    MEMBER [Time].[TimeFlow] as Aggregate ( { { ([Time].&[200811] : [Time].&[200821]) } * {[Version].&[Re]} }
     +  { { ([Time].&[200822] : [Time].&[200823]) } * {[Version].&[Pl]} })

    Or you could combine the whole thing with the Flow measure into a new calculated measure

    MEMBER
    [Measures].[AggregateFlow] as Sum ( { { ([Time].&[200811] : [Time].&[200821]) } * {[Version].&[Re]} }
     +  { { ([Time].&[200822] : [Time].&[200823]) } * {[Version].&[Pl]} } , [Measures].[Flow])

     


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Monday, February 2, 2009 12:27 AM
    Moderator