SQL Server Performance

Anchor Point in MDX

Discussion in 'Analysis Services/Data Warehousing' started by aeager, Feb 2, 2006.

  1. aeager New Member

    This is relatively easy to do in Excel but was wondering how to tackle this in MDX. I have a Sales Cube with 2 dimensions, [Purchase Year] and [First Purchase Year] with a measure of [Client Count]. The two Year dimensions are time dimensions. I want to anchor the selection of my [Purchase Year] so whatever year I select it will divide that number by the number of clients for the first purchase year, i.e.



    1AB C D E F
    ________________________________________________________________
    2Year 2002 2003 2004 2005
    ________________________________________________________________
    32002Client Count 2,700 1,800 1,500 1,200
    4% Renew 67% 56% 44%
    52003Client Count 5,000 2,500 2,200
    6% Renew 50% 44%
    72004Client Count 6,000 3,500
    8% Renew 58%
    92005Client Count 7,500
    10% Renew

    Therefore, Down the Page is the [First Purchase Year], Across is the [Purchase Year]. So in 2002 there were 2,700 clients who first purchased my product. Then in 2003, 1,800 of those 2,700 clients purchased the same product again (i.e. 67% of the original purchasers), then in 2004 there were 1,500 of the original 2,700 (56%) who purchased my product.
    My calculation is the number of clients in any purchase year divided by the number of initial clients who originally purchased in that year.

    In Excel this would be:

    D4 = D3/$C3
    E4 = E3/$C3
    F4 = F4/$C3 and so on.

    If I had to create a calculated member to do this, what would I do?
  2. Raulie New Member

    This is just off the top of my head but use it to start off your idea.....

    (Measures.[Client Count], OpeningPeriod([First Purchase Year].[Year]))/([Purchase Year].CurrentMember , [Measures].[Client Count])

    Raulie


Share This Page