MDX:平均使用 [英] MDX: avg advanced use

问题描述

对于某个问题，我已经达到了基本的MDX知识极限，如果有人有想法，我们将竭诚为您提供帮助

I am reaching the limit of my basic MDX knowledge on a problem, if anyone has an idea, every help will be welcome

情况

这是我要处理的层次结构.在我的fact_table中，我有一个[措施].[销售]措施.

This is the hierarchy I'd like to deal with. In my fact_table I have a [Measures].[Sales] measure.

[All Management].[TemplateMgt].[CityMgt].[DistricMgt].[StoreMgt]

[All Management].[TMP-00.002].[London].[DistricMgt].[Shoe001]
[All Management].[TMP-00.002].[London].[DistricMgt].[Hat001]
[All Management].[TMP-00.002].[London].[DistricMgt].[Electronic001]
[All Management].[TMP-00.002].[Paris].[DistricMgt].[Shoe001]
[All Management].[TMP-00.002].[Paris].[DistricMgt].[Hat001]
[All Management].[TMP-00.002].[Paris].[DistricMgt].[Electronic001]
[All Management].[TMP-00.002].[Madrid].[DistricMgt].[Shoe001]
[All Management].[TMP-00.002].[Madrid].[DistricMgt].[Hat001]
[All Management].[TMP-00.002].[Madrid].[DistricMgt].[Electronic001]

问题

对于给定的CityMgt，我想要三个值

For a given CityMgt, I would like to have three values

[度量].[Cur] :StoreMgt出售给定CityMgt的销售额(因此，在马德里，取值为[Shoe001]，[Hat001]，[Electronic001])

[Measures].[Cur]: StoreMgt's sales of the given CityMgt (So for Madrid, get the value [Shoe001], [Hat001], [Electronic001])

[Measures].[Avg] :具有相同TemplateMgt AVG([伦敦].[Shoe001] + [Paris].[Shoe001] + [Madrid]的StoreMgt的StoreMgt组的平均销售额) ].[Shoe001])

[Measures].[Avg]: the average sales of StoreMgt group by StoreMgt having the same TemplateMgt AVG([London].[Shoe001] + [Paris].[Shoe001] + [Madrid].[Shoe001])

[Measures].[Max] :具有相同TemplateMgt MAX([London].[Shoe001]，[Paris].[Shoe001]，[Madrid]的StroreMgt的最大销售价值. [Shoe001])

[Measures].[Max]: the max sales values of StroreMgt having the same TemplateMgt MAX([London].[Shoe001], [Paris].[Shoe001], [Madrid].[Shoe001])

换句话说，我想要一个具有这种结构的输出

In other word, I'd like to have an output that will have this structure

  Shoe001    |  Hat001     |  Electronic001
 ----------------------------------------------------
 CUR|AVG|MAX | CUR|AVG|MAX  | CUR|AVG|MAX
 ----------------------------------------------------

我到目前为止所得到的

WITH  MEMBER [Measures].[Cur] AS (...)
          MEMBER [Measures].[Avg] AS (...)
          MEMBER [Measures].[Max] AS (...)

SELECT {[Measures].[Cur], [Measures].[Avg], [Measures].[Max]} ON COLUMNS,
{FILTER({DESCENDANTS([All Management].CurrentMember, [StoreMgt])}, [All Management].Parent.Parent = "Madrid" } ON ROWS
from [MyCube]

我的问题是我不知道在Member属性Cur/Avg/Max中放入什么，因此我的数据可以按StoreMgt(一种groupby)进行处理

My problem is that I don't know what to put in the Member attributes Cur/Avg/Max so my datas can be treated per StoreMgt (a kind of groupby)

如果任何人都有资格参加，我将不胜感激.

If anyone can enligthenme, I will appreciate.

顺便说一句，

推荐答案

我不确定以下查询是否可以正常工作，希望它能传达出这一想法，

I am not completely sure that following query will work, hope it conveys the idea,

WITH MEMBER [All Management].[Sales_AVG] AS AVG({[All Management].Members},                           
                                                       [Measures].currentMember)
     MEMBER [All Management].[Sales_MAX] AS MAX({[All Management].Members},        
                                                        [Measure].currentMember)
SELECT {[Measures].[Sales]} ON COLUMNS,
       {[All Management].Members, [All Management].[Sales_AVG], 
         [All Management].[sales_Max]} ON ROWS FROM [MYCUBE] WHERE
       {DESCENDANTS([All Management].CurrentMember, [StoreMgt])}

这篇关于MDX:平均使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！