I want to get the 10 best xxx in a period (i.e. this quarter, last quarter, this year, last year...)
I have used topcount to get the top customers but it includes all the data from all years.
For example in foodmart to get top brands by sales I have:
set [TenBest] as 'TopCount( [Product].[Brand Name].Members, 10, [Unit Sales] )'
I assume I somehow use the [Time] dimension in the query to get the top brands for 1997, 1998, Q1 1998, Q3 1997...Then I can use the same syntax for toppercent, bottom....
Thanks,
Chris
Topcount for a period.
Mike Fulkerson
Chris,
The simplest method is to just use a tuple in place of the measure:
Using your example:
set [TenBest] as 'TopCount( [Product].[Brand Name].Members, 10, [Unit Sales] )'
For 1997
set [TenBest1997] as 'TopCount( [Product].[Brand Name].Members, 10, ([Unit Sales], [Time].[Calendar].[1997]) )'
HTH,
- Steve
Programm3r
OK that was a little over my head. I guess I need to do more reading. Where can I read about Background hierarchies and dynamic sets.
In the mean time I was hoping I was hoping I could say [Time].[Calendar].currentYear or simething like that... I just need a to get "Current Year", "Previous Year", "Current Quarter" and 'Previous Quarter"
I do like the idea of the dynamic set so that it would work with the scope of data I am viewing but I need to get a quick prototype up and running.
Thanks for all your help.
-Chris
I wish I started looking at this stuff a few years ago, it is awesome!
Johan Sörlin
You can do this with a "dynamic" set. If your client tool supports the creation of "Session" or "Query" scoped sets, then your top 10 set will reflect selections made on "Background" hierarchies (hierarchies that are not on rows or colums).
HTH,
- Steve
Rajesh batchu
How should I create is so that it is "Current Quarter" now mater when someone views it
Thanks.