The inspiration behind this blog entry is the comment received by Andrew on my blog post “Top to Bottom in MDX”. In that blog, I have explainded the use of Topcount and Bottomcount functions in MDX. The example therin shows the top five Resellers based on the Reseller Sales Amount. (I suggest to first go throgh the post to get more clear idea about the current topic.). Let’s revisit the query and the answer.
select
[Measures].[Reseller Sales Amount] on columns,
topcount
(
[Reseller].[Reseller].[Reseller], --Set_Expression
5, --Count
[Measures].[Reseller Sales Amount] --Numeric_Expression
) on rows
from
[Adventure Works]
Andrew’s Question…
“I need to look at resellers by laps time. How can I use the TopCount to look at top 20 reseller by Reseller Sales Amount where the laps time is the past 6 months (The last transaction happened six months ago).
Meaning, if we look at the above example, we want to list those top 20 resellers based on Reseller Sales Amount who have not sold anything in the past six months. Interesting, isn’t it, at least for the beginners like me who are in the learning mode of “The Da Vinci Code” named MDX J and I guess I have succeeded to crack the code.
Before looking at the solution, let’s drill down through the result of the above query to understand the situation well. The below query shows the Reseller Sales Amount for the above top five Resellers by month for the year 2004 (I have just filtered the data to limit the results)
with set [TopResellers] as
{
[Reseller].[Reseller].[Brakes and Gears],
[Reseller].[Reseller].[Excellent Riding Supplies],
[Reseller].[Reseller].[Vigorous Exercise Company],
[Reseller].[Reseller].[Totes & Baskets Company],
[Reseller].[Reseller].[Retail Mall]
}
select
[Measures].[Reseller Sales Amount] on columns,
crossjoin ([TopResellers],[Date].[Calendar].[Month]) on rows
from
[Adventure Works]
where
[Date].[Calendar Year].&[2004]
Query Analysis
In the above query, I have first created a Named Set “TopResellers” containing top five resellers we have got in the first query. You can read more on Named Sets in my blog “Implementing Calculations in SSAS using MDX – Part 2”. The crossjoin function shows the Resellers and Month names on row axis. Further a where slicer is used to limit the result for the Calendar Year 2004 (I have deliberately added this to have a small sample data). Look at the results and you will find that the Resellers “Excellent Riding Supplies” and “Vigorous Exercise Company” managed to sell in June 2004. But “Brakes and Gears”, “Totes & Baskets Company” and “Retail Mall” have not sold anything in the last three months i.e. June – August 2004. Our question is to find out these three (Top N Resellers who have not sold anything in the last three months). Here is the answer…
with set [Last3Months] as
LastPeriods (3, closingperiod([Date].[Calendar].[Month]))
select
[Measures].[Reseller Sales Amount] on columns,
filter
(
topcount
(
[Reseller].[Reseller].[Reseller], --Set_Expression
5, --Count
[Measures].[Reseller Sales Amount] --Numeric_Expression
),
isempty(sum([Last3Months],[Measures].[Reseller Sales Amount]))
)on rows
from
[Adventure Works]
Query Analysis
The query has returned the desired Resellers. But why only three where as we have asked for the top 5. This is because we have filtered the result to remove “Excellent Riding Supplies” and “Vigorous Exercise Company” using the filter function. First we have created a Named Set “Last3Months” to get the last three months using the Closingperiod and LastPeriods functions. The Closingperiod heregives us the last month value in the Date dimension which is August 2004. The LastPeiords then goes three periods (three months) back including August 2004 giving us a set June 2004 – August 2004. In the filter function, we are checking for the sum of Reseller Sales Amount for the last three months to be an empty value (no sales). Hope this is simple enough to understand.
Now to get the other two Resellers i.e. who have managed to sell in the past three months we can rewrite the query by just changing the second parameter to the filter function as follows,
with set [Last3Months] as
LastPeriods (3, closingperiod([Date].[Calendar].[Month]))
select
[Measures].[Reseller Sales Amount] on columns,
filter
(
topcount
(
[Reseller].[Reseller].[Reseller], --Set_Expression
5, --Count
[Measures].[Reseller Sales Amount] --Numeric_Expression
),
sum([Last3Months],[Measures].[Reseller Sales Amount]) > 0
)on rows
from
[Adventure Works]
Now, I guess, as we are pretty much comfortable with the filter function, let’s go back to our main question, “How can I use the TopCount to look at top 20 reseller by Reseller Sales Amount where the laps time is the past 6 months”. To answer this question we can use filter function along with Topcount to sort out only those Resellers who have not reported any sales in the past six months as follows,
with set [Last6Months] as
LastPeriods (6, closingperiod([Date].[Calendar].[Month]))
select
[Measures].[Reseller Sales Amount] on columns,
topcount
(
filter
(
[Reseller].[Reseller].[Reseller],
isempty(sum([Last6Months],[Measures].[Reseller Sales Amount]))
),
20,
[Measures].[Reseller Sales Amount]
) on rows
from
[Adventure Works]
Query Analysis
A named set Last6Months gives us a set of last six months in the Date dimension using the Closingperiod and Lastperiods functions. Notice the use of filter function inside Topcount to first filter out Resellers without any Sales in the past six months. Then the Topcount function fetches the top twenty Resellers based on the Sales Amount.
Summary
In the process of finding the solution to an issue, we have also learned some more MDX like creating Named Sets, functions like Crossjoin, Closingperiod, Lastperiods, filter, isempty etc. So here the filter function proved to be a powerful one. Hope I have answered Andrew’s question. The query may not be a perfect one and there can be more elegant ways of writing the same depending upon how deep we dive into MDX world.
Regards
Amit Karkhanis
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
Excellent post Amit