如何使用MDX执行不同的总和? [英] How to perform a Distinct Sum using MDX?

问题描述

所以我有这样的数据:

Date       EMPLOYEE_ID   HEADCOUNT   TERMINATIONS
1/31/2011        1            1             0 
2/28/2011        1            1             0
3/31/2011        1            1             0
4/30/2011        1            1             0
...
1/31/2012        1            1             0
2/28/2012        1            1             0
3/31/2012        1            1             0

1/31/2012        2            1             0
2/28/2011        2            1             0
3/31/2011        2            1             0
4/30/2011        2            0             1    

1/31/2012        3            1             0
2/28/2011        3            1             0
3/31/2011        3            1             0
4/30/2011        3            1             0
...
1/31/2012        3            1             0
2/28/2012        3            1             0
3/31/2012        3            1             0

我想总结人员总数，但是我需要删除employee_id总数中重复的条目.从数据中可以看到，employee_id 1在表中出现了很多次，但是我只想添加一次其headcount列.例如，如果我对年份进行汇总，则可能会使用以下查询获取报告:

And I want to sum up the headcount, but I need to remove the duplicate entries from the sum by the employee_id. From the data you can see employee_id 1 occurs many times in the table, but I only want to add its headcount column once. For example if I rolled up on year I might get a report using this query:

with member [Measures].[Distinct HeadCount] as
          ??? how do I define this???
select { [Date].[YEAR].children } on ROWS, 
       { [Measures].[Distinct HeadCount] } on COLUMNS 
       from [someCube]

它将产生以下输出:

YEAR    Distinct HeadCount
2011           3
2012           2

有什么想法如何使用MDX做到这一点?有没有一种方法可以控制每个员工在总和中使用哪一行?

Any ideas how to do this with MDX? Is there a way to control which row is used in the sum for each employee?

推荐答案

您可以使用像这样的表达式:

You can use an expression like this:

WITH MEMBER [Measures].[Distinct HeadCount] AS
    Sum(NonEmpty('the set of the employee ids', 'all the dates of the current year (ie [Date].[YEAR].CurrentMember)'), [Measures].[HeadCount])

如果您想使用更通用的表达式，可以使用以下代码:

If you want a more generic expression you can use this:

WITH MEMBER [Measures].[Distinct HeadCount] AS
    Sum(NonEmpty('the set of the employee ids', 
                 Descendants(Axis(0).Item(0).Item(0).Hierarchy.CurrentMember, Axis(0).Item(0).Item(0).Hierarchy.CurrentMember.Level, LEAVES)),
        IIf(IsLeaf(Axis(0).Item(0).Item(0).Hierarchy.CurrentMember),
            [Measures].[HeadCount],
            NULL))

这篇关于如何使用MDX执行不同的总和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！