MDX和PeriodsToDate [英] MDX and PeriodsToDate

问题描述

我是MDX的新手，所以我希望对MDX专家来说这是一个简单的问题.我有以下表达式SUM(PeriodsToDate([Accounting Date].[(All)])，[Measures].[Amount])，该表达式可以按预期工作.我需要添加一个基本上执行以下操作的过滤器或IIF语句:

I'm very new to MDX so I hope this is a simple question for a MDX expert. I have the following expression SUM(PeriodsToDate([Accounting Date].[(All)]),[Measures].[Amount]) which this piece works as expected. I need to add a filter or IIF statement that basically does the following:

IF 
  SUM(PeriodsToDate([Accounting Date].[(All)]),[Measures].[Amount]) < 0 
    Then 0
Else 
  SUM(PeriodsToDate([Accounting Date].[(All)]),[Measures].[Amount])

推荐答案

恐怕无法对预聚合的数据进行处理. MDX IIF函数对每个成员起作用，SQL IIF对每行起作用.您必须添加一个度量值，例如Amount，但iif([Amount]< 0,0，[Amount]).您也可以尝试下面的代码，不过，我怀疑结果是否正确:

I'm afraid it's impossible to do with pre-aggregated data. The MDX IIF function works per a member, the SQL IIF works per a row. You have to add a measure like Amount, but with iif([Amount] < 0,0,[Amount]). Also you may try the code below, nonetheless, I doubt it results properly:

SUM(
    NULL:[Accounting Date].[Calendar].CurrentMember,
    IIF(
        [Measures].[Amount] < 0,
        NULL,
        [Measures].[Amount] 
    )
)

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