Main Article    All About Nothing
DOWNLOAD THE CODE:
Download the Code 39705.zip

Problem: How would you write a query to find the top 10 customers based on unit sales who aren't single and female? Can you do it without listing any of the three other combinations (i.e., single male, married male, and married female)?

Solution: As Listing A shows, I answered this problem by filtering out the single, female customers before ranking the remaining customers with a TOPCOUNT() function. As you can see in the FILTER() function's second parameter, I used the virtual dimensions Marital Status and Gender, along with Unit Sales, to determine whether a particular customer in the top 10 was single and female. To be sure I had the right customers, I also created measures to return the marital status and gender for each displayed customer. I included those measures on the result's columns along with Unit Sales.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

In reviewing this solution, the code to filter only those customers that are not single or female doesn't make sense. When I change the filter condition to "> 0" from '= 0" and drop the Topcount, I see only customers that are single and female. (Kinda what I would expect). Then change it back to "> 0" and I see the other possible combinations (Married and Female or Single and Male, etc) and single and female if the unit sales is null. Doesn't make sense. Can someone give me a better explanation for this behavior.

Thanks, Jeff

Jeff Jones- October 03, 2003

 
 

ADS BY GOOGLE