SQL Server Performance Forum – Threads Archive

Anchor Point in MDX

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?

---

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

---

]]>