MDX Rank 请求返回意外结果

发布于 2024-09-28 02:13:04 字数 722 浏览 1 评论 0原文

我编写了这个相当简单的 MDX 请求来获取按每个性别的销售额排名：

WITH MEMBER [Measures].[rank] AS RANK(
      [Gender].CurrentMember,
      Order(
         [Gender].Members,
         [Measures].[salescount],
         BDESC
      ),
      [Measures].[salescount]
   )
SELECT [Gender].Members ON COLUMNS,
[Measures].[rank] ON ROWS
FROM [SalesAnalysis]

问题是结果是错误的，我知道 M 的销售额比 F 多：

Axis #0:
  {}
Axis #1:
  {[Measures].[rank]}
Axis #2:
  {[Gender].[All Genders]}
  {[Gender].[F]}
  {[Gender].[M]}
Row #0: 1
Row #1: 2
Row #2: 3

如果我使用“国家/地区”而不是“性别”，则会出现同样的问题：排名是根据字母顺序给出的，而不是基于销售计数。 （即：所有国家/地区均按字母顺序列出，排名为 1、2、3、4、...）

如何修复请求以正确显示每个性别的销售额排名？

原文

I wrote this fairly simple MDX request to get the rank by sales count of each gender:

WITH MEMBER [Measures].[rank] AS RANK(
      [Gender].CurrentMember,
      Order(
         [Gender].Members,
         [Measures].[salescount],
         BDESC
      ),
      [Measures].[salescount]
   )
SELECT [Gender].Members ON COLUMNS,
[Measures].[rank] ON ROWS
FROM [SalesAnalysis]

The problem is that the result is wrong, I know M has more sales than F:

Axis #0:
  {}
Axis #1:
  {[Measures].[rank]}
Axis #2:
  {[Gender].[All Genders]}
  {[Gender].[F]}
  {[Gender].[M]}
Row #0: 1
Row #1: 2
Row #2: 3

If I use "Country" instead of "Gender", same problem: the rank is given according to the alphabetical order, instead of based on the sales count. (ie: all countries come listed in alphabetical order, with rank 1,2,3,4,...)

How can I fix the request to correctly show each gender's salescount rank?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

挽梦忆笙歌 2024-10-05 02:13:04

好的，我找到了解决方案：

WITH MEMBER [Measures].[rank] AS RANK(
      [Reseller].CurrentMember,
      Order(
         [Reseller].Members,
         [Measures].[salescount],
         BDESC
      ),
      [Measures].[salescount]
   )
SELECT Order(
         [Reseller].Members,
         [Measures].[salescount],
         BDESC
      ).Item([theShopWhoseRankIWant]) ON COLUMNS,
[Measures].[rank] ON ROWS
FROM [SalesAnalysis]

抱歉产生噪音，我希望这可以帮助某人:-)

OK, I have found the solution:

WITH MEMBER [Measures].[rank] AS RANK(
      [Reseller].CurrentMember,
      Order(
         [Reseller].Members,
         [Measures].[salescount],
         BDESC
      ),
      [Measures].[salescount]
   )
SELECT Order(
         [Reseller].Members,
         [Measures].[salescount],
         BDESC
      ).Item([theShopWhoseRankIWant]) ON COLUMNS,
[Measures].[rank] ON ROWS
FROM [SalesAnalysis]

Sorry for the noise, I hope this might help someone :-)

回复收藏 0 原文

~没有更多了~