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?
{
TOPCOUNT(
{ TM1FILTERBYLEVEL(
{tm1subsetall( [Month] )}
,0)}
,5
, [Sales Test].([Sales Analysis Measure].[Quantity]))
}
thanks a lot.
Use TOPCOUNT to get the top ones in order of the year you want
-
- 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
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:
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.
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]))
}
Declan Rodger
-
- 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
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]))
}
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]))
}