计算尺寸/尺寸 [英] Calculated measure/dimension

问题描述

我对MDX相对较新，正在尝试完成我认为应该很容易的事情，但是我没有找到任何解决方案.

I'm relatively new to MDX and am trying to accomplish what I think should be an easy thing, but I haven't found any solution.

我有一个销售多维数据集，度量之一是利润，它可以是负数或正数.我想获得一种有效地衡量正利润总和的度量，即仅在新度量中包括具有正利润的利润数字.

I have a sales cube and one of the measure is profit which can be negative or positive. I want to get one measure which is effectively the sum of positive profit, i.e. only include in the new measure those profit numbers that have a positive profit.

这里的技巧是在行详细信息级别上，例如

The trick here is this is on the row detail level and something like

将成员测量.PositivePNL作为IIF(测量.PNL> 0，测量.PNL，0)

WITH MEMBER Measures.PositivePNL as IIF (Measures.PNL > 0, Measures.PNL,0)

不起作用，因为它仅适用于合计数字

doesn't work as that only works with the aggregate number

推荐答案

我应该提到我正在使用Mondrian/MySQL.我得出了类似的结论，但是找到了一种使用SQL键表达式创建新的退化维的方法(因此我实际上不需要在表中添加列):

I should have mentioned that I am using Mondrian/MySQL. I came to similar conclusion but found a way to create a new degenerate dimension using an SQL key expression (so I don't actually need to add column to table):

<Dimension name="PNLCategory">
    <Hierarchy hasAll="true">
        <Level name="PNLCategory" column="pnlCategory" uniqueMembers="true">
            <KeyExpression>
                <SQL dialect="generic"> <![CDATA[IF(pnl >= 0,'Winner','Loser')]]></SQL>
            </KeyExpression>
        </Level>
    </Hierarchy>
</Dimension>

现在可以轻松地计算出成员了:

Now it becomes easy to do the calculated member:

<CalculatedMember name="WinnersCountByPNL" aggregator="count" dimension="Measures">
  <Formula>([PNLCategory].[Winner], Measures.PNL)</Formula>
  <CalculatedMemberProperty name="FORMAT_STRING" value="$#,###"/>
  <CalculatedMemberProperty name="DATATYPE" value="Numeric"/>
</CalculatedMember>     

所以在这里，我将求和仅限于获胜者"，而好处是Mondrian不会从行表中检索计数(事实)条目.

so here I restrict the summation to only "winners" and the benefit is that Mondrian will not retrieve count(fact) entries from the row table.

这篇关于计算尺寸/尺寸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！