如何为计算成员构建动态MDX公式？ [英] How to build a dynamic MDX formula for a calculated member?

问题描述

我正在尝试创建一个计算会员以获得上周的总结。
没有问题得到周的值（我需要它是两位数，即'05'，所以添加100-1）

I am trying to create a Calculated Member to get a sum up to last week. No problem to get the week value (I need it to be two digits i.e. '05', so adding 100-1 )

with
Member [Measures].Week as 
  'right(str(int(99+datepart ( ''ww'', Now()))),2)' 

- 按预期工作

member [Measures].SalesUpToWeek as
  'strtomember(
     "aggregate(periodstodate([Dim].[2015],[Dim].[2015].[" + ([Measures].Week) + "]),[Measures].[Sales])")'

我得到文字值

aggregate(
    periodstodate([Dim].[2015],[Dim].[2015].[25])
   ,[Measures].[Sales]
)

我需要的是这个 MDX 计算的价值。

What I need is the value of this MDX calculation.

所有其他尝试都会结束语法错误。作为一个例子

All other attempts end up with a syntax error. Just as an example

member [Measures].SumToWeek as 
 'aggregate(
     periodstodate(
       [Dim].[2015],[Dim].[2015].[' + strtomember([Measures].Week) + '])
   ,[Measures].[Sales])'   

错误

错误在第2行第0列。遇到：之后：[\\\

Lexical error at line 2, column 0. Encountered: after : "[\n"

任何想法？

推荐答案

我对Pentaho论坛有一个有趣的建议，指出我有一个很好的博客文章，允许写一个优雅的解决方案：

I got an interesting suggestion on Pentaho forums that pointed me to a nice blog post which allowed to write an elegant solution:

http：/ /diethardsteiner.blogspot.com.es/2009/10/current-date-function-on-mondrian.html

with
member [Measures].sm as aggregate(
    periodstodate(
        currentdatemember([Dim],'["Dim"]\.[yyyy]'),                     -- current year
        currentdatemember([Dim],'["Dim"]\.[yyyy]\.[ww]').lag(1)   -- previous week
    ),
    [Measures].[Sales])

Thks

这篇关于如何为计算成员构建动态MDX公式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！