MDX Question - Using Top Count with Rank/Order Function
0
How do I use a top count so it only returns the first 2 records with the highest sales for each sales territory group (North America, Pacific, etc...)
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
ON COLUMNS,
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
ON ROWS
FROM
[Adventure Works]
The end result should be that it removes the highlighted row below.
mdx mdx-query
asked Nov 14 '18 at 20:28
user2607686user2607686
2218
add a comment |
0
How do I use a top count so it only returns the first 2 records with the highest sales for each sales territory group (North America, Pacific, etc...)
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
ON COLUMNS,
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
ON ROWS
FROM
[Adventure Works]
The end result should be that it removes the highlighted row below.
mdx mdx-query
asked Nov 14 '18 at 20:28
user2607686user2607686
2218
add a comment |
0
0
0
How do I use a top count so it only returns the first 2 records with the highest sales for each sales territory group (North America, Pacific, etc...)
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
ON COLUMNS,
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
ON ROWS
FROM
[Adventure Works]
The end result should be that it removes the highlighted row below.
mdx mdx-query
asked Nov 14 '18 at 20:28
user2607686user2607686
2218
How do I use a top count so it only returns the first 2 records with the highest sales for each sales territory group (North America, Pacific, etc...)
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
ON COLUMNS,
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
ON ROWS
FROM
[Adventure Works]
The end result should be that it removes the highlighted row below.
mdx mdx-query
mdx mdx-query
asked Nov 14 '18 at 20:28
user2607686user2607686
2218
asked Nov 14 '18 at 20:28
user2607686user2607686
2218
asked Nov 14 '18 at 20:28
user2607686user2607686
2218
asked Nov 14 '18 at 20:28
user2607686user2607686
2218
asked Nov 14 '18 at 20:28
user2607686user2607686
2218
2218
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
Try now
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
ON COLUMNS,
filter(///Added this
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
,[Measures].[Group_Territory_Ranking_InternetSales]<3) ///Added this
ON ROWS
FROM
[Adventure Works]
answered Nov 22 '18 at 20:45
MoazRubMoazRub
9021413
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53308272%2fmdx-question-using-top-count-with-rank-order-function%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Try now
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
ON COLUMNS,
filter(///Added this
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
,[Measures].[Group_Territory_Ranking_InternetSales]<3) ///Added this
ON ROWS
FROM
[Adventure Works]
answered Nov 22 '18 at 20:45
MoazRubMoazRub
9021413
add a comment |
0
Try now
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
ON COLUMNS,
filter(///Added this
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
,[Measures].[Group_Territory_Ranking_InternetSales]<3) ///Added this
ON ROWS
FROM
[Adventure Works]
answered Nov 22 '18 at 20:45
MoazRubMoazRub
9021413
add a comment |
0
0
0
Try now
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
ON COLUMNS,
filter(///Added this
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
,[Measures].[Group_Territory_Ranking_InternetSales]<3) ///Added this
ON ROWS
FROM
[Adventure Works]
answered Nov 22 '18 at 20:45
MoazRubMoazRub
9021413
Try now
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
ON COLUMNS,
filter(///Added this
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
,[Measures].[Group_Territory_Ranking_InternetSales]<3) ///Added this
ON ROWS
FROM
[Adventure Works]
answered Nov 22 '18 at 20:45
MoazRubMoazRub
9021413
answered Nov 22 '18 at 20:45
MoazRubMoazRub
9021413
answered Nov 22 '18 at 20:45
MoazRubMoazRub
9021413
answered Nov 22 '18 at 20:45
MoazRubMoazRub
9021413
9021413
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53308272%2fmdx-question-using-top-count-with-rank-order-function%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
