Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

What happend to the top 10 products previous years ?

Posted by thomasivarssonmalmo on July 27, 2008

I have written previously about the TOPCOUNT() function and in this blog post I will extend that with the previous development for the top 10 products.  This is also the second part of my previous blog post about analyzing product volatility.
 
Let us start with the TOPCOUNT function and later extend that with a calculated measure using the RANK() function.
 
Here we have the top 10 selling products, for the year 2004, in the Adventure Works cube. 
 
WITH Set TopTenProducts2004 As
TopCount(Descendants([Product].[Product Categories],[Product].[Product Categories].[Product]) , 10,
([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
 
Select {[Date].[Calendar Year].&[2004]} On 0,
TopTenProducts2004 On 1
From [Adventure Works]
Where (Measures.[Internet Sales Amount]);
 
I have been explicit about putting the year 2004 on column since you have the freedom to put any year you want there.
 
If you would like to check this with the data source you can use this TSQL statement:
 
Select p.ProductAlternateKey, p.EnglishProductName, Sum(f.SalesAmount)as SalesAmount
From DimProduct p Join dbo.FactInternetSales f On
p.ProductKey  = f.ProductKey join dimTime t On
f.OrderDateKey = t.TimeKey
Where Year(t.FullDateAlterNateKey) = 2004
Group By p.ProductAlternateKey, p.EnglishProductName
Order By  Sum(f.SalesAmount) DESC
 
And now to the final example. How can I show the previous years ranking for the top 10 products in 2004?
 
WITH Set TopTenProducts2004 As
TopCount(Descendants([Product].[Product Categories],[Product].[Product Categories].[Product]) , 10,
([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
 
MEMBER [Measures].[ProductRank2004] AS
RANK([Product].[Product Categories].CurrentMember, TopTenProducts2004)
 
Set TopTenProducts2003 As
TopCount(Descendants([Product].[Product Categories],[Product].[Product Categories].[Product]) , 10,
([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004].Lag(1)))
 
MEMBER [Measures].[ProductRank2003] AS
RANK([Product].[Product Categories].CurrentMember, TopTenProducts2003)
 
Set TopTenProducts2002 As
TopCount(Descendants([Product].[Product Categories],[Product].[Product Categories].[Product]) , 10,
([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004].Lag(2)))
 
MEMBER [Measures].[ProductRank2002] AS
RANK([Product].[Product Categories].CurrentMember, TopTenProducts2002)
Select {[Measures].[Internet Sales Amount],[Measures].[ProductRank2004],[Measures].[ProductRank2003],[Measures].[ProductRank2002]} On 0,
TopTenProducts2004 On 1
From [Adventure Works]
Where ([Date].[Calendar Year].&[2004]);
 
The Query
 
 
What I do is to create sets for the previous years simply by repeating the same named sets for each year of interest but by only adding the LAG() function to each named set.  This gives me the top 10 products for 2004 but their sales for each previous year I add. Each top 10 set is placed in the calculated measure with the RANK() function. If the product has not been sold the RANK() function will return 0.
 
In the result you will see that some of the top 10 products in 2004 had another ranking in 2003 and none of them were sold in 2002.
 
I am sure that the code above can be improved and optimized so do not not hesitate to add you comments. 
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: