Use TOPCOUNT to get the top ones in order of the year you want

Post Reply
Niko
Posts: 50
Joined: Tue Feb 15, 2022 6:43 am
OLAP Product: IBM Cognos TM1
Version: PA 2.09 / TM1 11.7.00000.42
Excel Version: EXCEL 2019
Location: Asia

Use TOPCOUNT to get the top ones in order of the year you want

Post by Niko »

cube name :Sales Test
dimension1 name :"Year"
dimension2 name :"Month"
dimension2 name : "Sales Analysis Measure" which have the "Quantity" Measure.
anyone know :
I want to build up a subset use topcount fx,because i want to do TopN analysis, now i want to get top 5 month sort by quantity using the mdx following, i see its The default value is in 2012 order , i want to sort by 2013's quantity , how i shou modify the mdx? :D
{
TOPCOUNT(
{ TM1FILTERBYLEVEL(
{tm1subsetall( [Month] )}
,0)}
,5
, [Sales Test].([Sales Analysis Measure].[Quantity]))
}
thanks a lot.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Use TOPCOUNT to get the top ones in order of the year you want

Post by declanr »

You just need to specify the specific elements for any dimension that's in the Sales Test cube.
If the Year dimension is called "Year" it would look something like below:

Code: Select all

{
TOPCOUNT(
{ TM1FILTERBYLEVEL(
{tm1subsetall( [Month] )}
,0)}
,5
, [Sales Test].([Year].[2013],[Sales Analysis Measure].[Quantity]))
}
It's a good idea to specify all the dimensions if you can. The code you have written assumes that the subset is going to be used in a cube view where Months are on row or column. And other dimensions such as Year are in the Title Elements, when that is the case your original MDX would dynamically change which year it filters by, based on which single year element is in the title dimension.
Declan Rodger
Niko
Posts: 50
Joined: Tue Feb 15, 2022 6:43 am
OLAP Product: IBM Cognos TM1
Version: PA 2.09 / TM1 11.7.00000.42
Excel Version: EXCEL 2019
Location: Asia

Re: Use TOPCOUNT to get the top ones in order of the year you want

Post by Niko »

thx. i learn a lot from your code.
my cube's all dimension is
dim1 = Year,(2012,2013...2018)
dim2 = Month,(1,2,3...12)
dim3 = Customer,(7-eleven,exxon,mapco and so on)
dim4 = Sales Analysis Measure (include measure quantity)
so i try to get top 5 ,sort by "quantity,2013,7-eleven"
the code try success :

{
TOPCOUNT(
{ TM1FILTERBYLEVEL(
{tm1subsetall( [Month] )}
,0)}
,5
, [Sales Test].([Year].[2013],[Customer].[7-Eleven],[Sales Analysis Measure].[Quantity]))
}
Post Reply