英文:
MDX - calculating distinct count on date range
问题
我有一个多维立方体，其中包括以下维度：
[Person]（人员）[Arrival Date]（到达日期）[Departure Date]（离开日期）
还有一个度量：[Measures].[persons #D]，表示不同人员的计数，并包含了包含人员到达和离开日期的事实表。
我需要计算在用户选择的日期下，“仍然在这里”的不同人员数量。这意味着我需要计算那些在用户选择的日期之前到达并且在用户选择的日期之后离开的人员的数量。
用户选择的日期存储在 [Measure].[EndOfPeriodDate] 中。
在DAX/表格模型中，我可以轻松地做到这一点，类似于以下方式：
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月3日],[Arrival].[Arrival].[Day].[2019年5月8日]:null ), [Measures].[#People])
SELECT
toto on 0
FROM [Cube] 
文档参考：
希望有所帮助！
1: https://doc.iccube.com/?ic3topic=server.mdx.SubCubeUnion
2: https://doc.iccube.com/?ic3topic=server.mdx.Eval
英文:
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!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论