http://www.sqlservercentral.com/blogs/sqldownsouth/2010/06/30/mdx-puzzle-_2300_2/

Printed 2014/11/28 10:06AM

MDX Puzzle #2

By Patrick LeBlanc, 2010/06/30

Now that we have our feet wet, let’s get started with the next puzzle.  In this puzzle we will write a query that returns Percentage Growth Year over Year.  For example, if sales were $258,056 in 2006 and $389,456 in 2007, then the percentage growth would be (2007 sales – 2006 sales)/2006 sales.  Here are the requirements:

Columns:  Internet Sales Amount and Percentage Growth (calculated measure)

Rows:  Calendar Years

Filter:  Only Show United States Bike Sales

Hint:  You can use the CREATE MEMBER statement to perform the calculation and use it in your query.   You may also need to use a CASE Statement to address a Divide By Zero error.

Remember, don’t post you solutions here.  Save them for my solution post.  I will post it along with the steps that was taken to solve the puzzle in a couple of days.   Here is a screen shot of what the results should look like:

 image

Don’t forget to check back in a couple days for the solution.

Talk to you soon,

Patrick LeBlanc

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.