9. Show all the product brands for the past four quarters, highlighting brands that sold in the bottom 10 percent for that quarter. Cell properties are a convenient way to perform exception highlighting (i.e., changing the font or color to draw the user's attention to important information) in a query. In Listing 9, I added the cell property FONT_FLAGS to the calculated member HLUnit Sales to boldface the unit sales numbers in the bottom 10 percent of all product brands for that quarter. Because a cell property's value can be an MDX expression, you can perform conditional logic to determine whether the font will be roman or boldface. In this case, the condition logic determines whether the current brand is in the bottom 10 percent by doing a set intersect with the full list of brands in the bottom 10 percent. If the intersect yields a count of 0, the brand isn't among the bottom members and will appear in a roman font. If the count is 1, the brand is among the bottom 10 percent, and the value will appear in boldface.
10. Show the additional sales growth per month for a given promotion, and compare sales with those of parallel months in the previous quarter. This example shows how to find the range of monthly sales that a sales promotion affects. The PromoMonths set finds the precise months that a promotion affected. To ensure that the list of months is consecutive, use the Range() function to include mid-promotion months whose sales were unaffected.
For each month of the promotion, Listing 10's query displays the total unit sales, the total unit sales for the parallel month in the previous quarter, the growth (in absolute terms, rather than a percentage) from quarter to quarter, and the amount of the growth that the promotion caused. The ParallelPeriod() function finds the parallel month in the previous quarter. (Typically, you'd look for the same month in the previous year, but the Sales cube doesn't contain enough months to go back that far.) For example, if the current month is the second month in the current quarter, ParallelPeriod() will return the second month in the previous quarter.
11. Which product brands have store sales that exceed store cost by at least 160 percent? The query in Listing 11 demonstrates a technique to perform advanced filtering of the product-brand members. First, the query creates a SalesRatio measure to determine the percent by which store sales exceed store cost. Then, the query uses this ratio—combined with the Filter() function—to determine which brands exceed 160 percent. This example also demonstrates the use of the FORMAT_STRING cell property, which controls the format of a number for display. In this case, FORMAT_STRING displays the number as a percentage.
12. Which product brands' sales have grown from one quarter to the next by more than 50 percent? To make Listing 12's query more readable, I created two new measures, CurrQSales and PrevQSales, which return the current quarter's sales and the previous quarter's sales, respectively. The Growth measure uses these two new measures to determine the growth of the current product's sales from the previous quarter to the current quarter. The query then uses this growth value to filter all the product brands and isolate those with greater than 50 percent sales growth. The resulting display shows the previous quarter's sales, the current quarter's sales, and the percent growth from quarter to quarter for each brand that meets the growth criteria.
13. Show the top 10 product brands and the bottom 10 product brands, along with their unit sales and ranking numbers. Ranking is a common task in analysis because the largest and smallest items in a group are often the ones most relevant to decision making. The query in Listing 13 creates an ordered list of all product brands and displays the top 10 and bottom 10 from this list. The query also uses the Rank() function to display each brand's position in the ordered list. These ranking numbers are useful because the numbers convey the total number of product brands.
14. Compare a particular product trend with the average of all other products of the same brand and with all products of all brands. This example demonstrates a baseline analysis, in which you compare a trend in your business with some other known trend. An example in stock analysis is to compare a particular company's performance with the S&P 500, the Dow Jones industrial average, or the average of all companies in the same marketplace. The query in Listing 14 displays the trend of the past four quarters of the Ebony Plums product, along with the average of all Ebony products and all products that FoodMart carries.
15. List the top 10 middle-tier brands (i.e., the brands with between 500 and 3000 unit sales for the past 12 months). This example demonstrates a nested filter. The query in Listing 15 uses a filter to determine which brands make up the middle tier, then uses the TopCount() filter function to find the top 10 brands of that group.
These examples should equip you with more tools for using MDX to conquer complex business problems. For more examples, go to http://www.sqlmag.com and view previous Mastering Analysis columns. Each month, I've provided at least one example in the form of the MDX Puzzle. Also, you can find more detailed information about most of the concepts I've presented this month. If you have a good example, send it my way, and I'll try to use it in a future column.