MDX日期之间的差值,非空且具有相同维 [英] MDX Difference between dates non null and of the same dimension
本文介绍了MDX日期之间的差值,非空且具有相同维的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要计算两次计数之间的天数,以便数据集为
Hi I need to calculate No. of days between Counts so the dataset is
我创建了最后一个日期的成员,这作为一个开始返回整个数据集中的最后一个日期,但不确定如何继续.在MDX中可以这样做吗?如果是这样怎么办?
I have created a member of the last date, this returns the last date across the whole dataset as a start but not sure how to proceed. Is it possible to do this in MDX? if so how?
谢谢
MEMBER [Measures].[Last date] AS
MAX([Date].[Full Date].[Full Date].MEMBERS,
iif( [Measures].[Count] = 0,
null,
[Date].[Full Date].CurrentMember.MemberValue
)
)
推荐答案
以下示例将有所帮助.但是请注意,它将从本月初算起.因此,如果有一个订单在5月5日,而最后一个订单在4月28日,那么差异将是4而不是7.
The following example will help. But please note that it will count from the begining of the month. So if there was an order was on 5 may and the last order was on 28 April then the diffrence would be 4 not 7.
with member measures.MonthDate as
[Date].[Day of Month].CurrentMember.Properties ("Member_Value",TYPED)
member measures.LastOrderDate as
(max(
FILTER(
[Date].[Day of Month].firstSibling:[Date].[Day of Month].currentmember.lag(1)
,[Measures].[Internet Order Count] > 0)
,measures.MonthDate)
)
member measures.DaysToLastOrder as
(max(
FILTER(
[Date].[Day of Month].firstSibling:[Date].[Day of Month].currentmember
,[Measures].[Internet Order Count] > 0)
,measures.MonthDate)
-measures.LastOrderDate
)
select
{
[Measures].[Internet Order Count],measures.DaysToLastOrder
}
on columns,
nonempty (([Date].[Month of Year].[Month of Year],[Date].[Day of Month].[Day of Month]),[Measures].[Internet Order Count])
on rows
from
[Adventure Works]
where ([Date].[Calendar Year].&[2012],[Customer].[City].&[London]&[ENG],[Product].[Subcategory].&[1]--,[Date].[Month of Year].&[11]
)
这篇关于MDX日期之间的差值,非空且具有相同维的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文