英文:
MDX - calculating distinct count on date range
问题
我有一个多维立方体，包含以下维度：[Person]（人员）、[Arrival Date]（到达日期）和[Departure Date]（离开日期），以及度量[Measures].[persons #D]（人员的去重计数）。事实表包含人员离开和到达的日期。
我需要计算在用户选择的日期时“在此停留”的人员的去重计数。也就是说，我需要计算在用户选择日期之前到达并在之后离开的人员数量。
用户选择的日期存储在[Measure].[EndOfPeriodDate]中。
在DAX/Tabular模型中，我可以很容易地实现这个目标，类似于以下代码：
CALCULATE(COUNTA([Person]); FILTER(FactTable;
('FactTable'[Arrival Date] < [EndOfPeriodDate]) &&
'FactTable'[Departure Date] > [EndOfPeriodDate])))
但是我真的不知道如何在MDX中编写相同的代码（我正在使用icCube）。
我编写了以下度量，并且它运行良好：
create member [Measures].[PersonsAtEndOfPeriod] AS
aggregate
(
NULL:[Arrival Date].[Arrival Date].[Day].lookupByName([measures].[EndOfPeriodDate])
,[Measures].[Persons #D]
)
但这只是第一部分。我需要添加第二部分的条件：
([Departure Date].[Departure Date].[Day].lookupByName([measures].[EndOfPeriodDate]:NULL)
我认为它可能是这样的：
NULL:[Arrival Date].[Arrival Date].[Day].lookupByName([measures].[EndOfPeriodDate]) +
([Departure Date].[Departure Date].[Day].lookupByName([measures].[EndOfPeriodDate]:NULL)
但它不起作用...
问题：如何在我的度量中添加第二个（或多个）条件？
提前感谢！
英文:
i've got a cube (multidimensional) with
[Person],
[Arrival Date] and
[Departure Date] dimensions,
[Measures].[persons #D] - distinct count of [Person], and fact table containing dates of person's departures and arrivals.
I need to calculate distinct count of persons that "staying here" at user-picked date.
that mean i need count Persons that have [Arrival Date] before and [Departure Date] after user-picked date.
User selected date is stored in [Measure].[EndOfPeriodDate].
In Dax/tabular model i can make it easy. something like that:
CALCULATE(COUNTA([Person]); FILTER(FactTable;
('FactTable'[Arrival Date] < [EndOfPeriodDate]) &&
'FactTable'[Departure Date] > [EndOfPeriodDate])))
but i really can't understand how write the same in MDX (i'm working with icCube).
I wrote following measure and it works well:
create member [Measures].[PersonsAtEndOfPeriod] AS
aggregate
(
NULL:[Arrival Date].[Arrival Date].[Day].lookupByName([measures].[EndOfPeriodDate])
,[Measures].[Persons #D]
)
but its only first part. i need to add the second part of my condition
([Departure Date].[Departure Date].[Day].lookupByName([measures].[EndOfPeriodDate]:NULL)
i suppose it might be look like :
NULL:[Arrival Date].[Arrival Date].[Day].lookupByName([measures].[EndOfPeriodDate]) +
([Departure Date].[Departure Date].[Day].lookupByName([measures].[EndOfPeriodDate]:NULL)
but it doesn't work...
QUESTION: how to add second (or more than 2) condition(s) in my measure?
Thanks in advance!
答案1
得分: 1
首先，度量值[Measures].[#People]应该是对人员唯一标识进行去重计数。
定义如下：
WITH
MEMBER toto as eval(SubCubeUnion(null:[Departure].[Departure].[Day].[2019-11-03],[Arrival].[Arrival].[Day].[2019-05-08]:null ), [Measures].[#People])
SELECT
toto on 0
FROM [Cube]
文档参考：
希望对你有帮助！
英文:
First, the measure [Measures].[#People] should be a distinct count on the person unique id.
The definition should be:
WITH
MEMBER toto as eval(SubCubeUnion(null:[Departure].[Departure].[Day].[3 Nov 2019],[Arrival].[Arrival].[Day].[8 May 2019]:null ), [Measures].[#People])
SELECT
toto on 0
FROM [Cube] 
Doc references:
Hope it helps!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论