Post reply

MDX -> Problems with ranking in this query, any ideas on why the rank always returns nulls for both prev and current?

  • January 26, 2012 at 12:30 pm

    #146512

    I think I am just missing something small, but can't seem to put my finger on it.....Hopefully a different set of eyes can see what I am missing.

    WITH MEMBER [Measures].[PrevDayRevenue] AS

    ( [Measures].[Revenue], ParallelPeriod ([Date Link].[PK Date].[PK Date],1))

    SET [RevRankPrevOrder] AS

    ORDER (

    [Customer].[Customer Id].Members ,

    [Measures].[PrevDayRevenue],

    BDESC)

    MEMBER [Measures].[RANKRevenuePrevOrder] AS RANK([Customer].CurrentMember, [RevRankPrevOrder])

    SET [RevRankCurrOrder] AS

    ORDER (

    [Customer].[Customer Id].Members ,

    [Measures].[Revenue],

    BDESC)

    MEMBER [Measures].[RANKRevenueCurrOrder] AS RANK([Customer].CurrentMember, [RevRankCurrOrder])

    SELECT NON EMPTY { [Measures].[Revenue], [Measures].[PrevDayRevenue], [Measures].[RANKRevenuePrevOrder], [Measures].[RANKRevenueCurrOrder] } ON COLUMNS,

    NON EMPTY { ( [RevRankCurrOrder] ) } ON ROWS

    FROM [DW]

    WHERE {[Date Link].[PK Date].&[2012-01-08T00:00:00]}

  • January 26, 2012 at 1:18 pm

    #1439013

    For any one wondering about this here is the answer. :

    WITH MEMBER [Measures].[PrevDayRevenue] AS

    ( [Measures].[Revenue], ParallelPeriod ([Date Link].[PK Date].[PK Date],1))

    SET [RevRankPrevOrder] AS

    ORDER (

    [Customer].[Customer Id].CHILDREN ,

    [Measures].[PrevDayRevenue],

    BDESC)

    MEMBER [Measures].[RANKRevenuePrevOrder] AS

    RANK(

    [Customer].[Customer Id].CurrentMember,

    [RevRankPrevOrder])

    SET [RevRankCurrOrder] AS

    ORDER (

    [Customer].[Customer Id].CHILDREN,

    [Measures].[Revenue],

    BDESC)

    MEMBER [Measures].[RANKRevenueCurrOrder] AS RANK([Customer].[Customer Id].CurrentMember, [RevRankCurrOrder])

    SELECT NON EMPTY { [Measures].[Revenue], [Measures].[PrevDayRevenue], [Measures].[RANKRevenuePrevOrder], [Measures].[RANKRevenueCurrOrder] } ON COLUMNS,

    NON EMPTY { ( [RevRankCurrOrder] ) } ON ROWS

    FROM [DW]

    WHERE {[Date Link].[PK Date].&[2012-01-10T00:00:00]}

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply