MDX-在当前期间处于活动状态，而在上一个期间未处于活动状态 [英] MDX - active in current period, and not active in previous period

问题描述

在MDX中最有效的方法是什么?我知道您可以创建两个计算出的度量，并在选定的时间维度当前时间段内处于活动状态，而在前一个时间段内处于活动状态，然后对复杂条件进行过滤.

What is the most efficient way to do this in MDX? I know you can create 2 calculated measures, having active with the time dimension current period selected, and another having the previous period, and then do a filter with a complex condition.

但是，其他更有效的功能又如何呢?有什么建议吗?

我的目标是创建一组计算得出的度量标准，这将有助于客户分析人员.主要的2个维度是[日历]，当然还有[客户].我有一个事实，其中包含针对客户的活动.这是我们计算所基于的3个实体.

My goal is to create a set of calculated measures, which would help customer analysts. The main 2 dimensions, for these are [Calendar], and obviously [Customer]. I have a fact, which contains activity for customers. These are the 3 entities that our calculations would be based on.

推荐答案

这是来自Chris Webb的文章:

This is from an article by Chris Webb: http://cwebbbi.wordpress.com/2010/10/08/counting-new-and-returning-customers-in-mdx/

有关如何对其进行优化的后续文章: http://cwebbbi.wordpress.com/2013/06/28/optimising-returning-customers-calculations-in-mdx/

With a follow up article on how to optimize it: http://cwebbbi.wordpress.com/2013/06/28/optimising-returning-customers-calculations-in-mdx/

WITH
MEMBER MEASURES.[Returning Customers] AS
COUNT(
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, {[Measures].[Internet Sales Amount]}
* {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
)
)
MEMBER MEASURES.[New Customers] AS
[Measures].[Customer Count] – MEASURES.[Returning Customers]
SELECT
{[Measures].[Customer Count]
, MEASURES.[Returning Customers]
, MEASURES.[New Customers]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

这篇关于MDX-在当前期间处于活动状态，而在上一个期间未处于活动状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！