计算时间维度节点中的天数-总计 [英] Calculating the number of days in a time dimension node - with Grand Total

问题描述

我需要知道时间范围内用于计算加权平均值的天数.我在计算的度量中使用以下表达式来获取当前维成员中的天数:

I need to know the number of days in a time dimension period for calculating weighted averages. I am using the following expression in a calculated measure to obtain the number of days in the current dimension member:

Count(
    Descendants(
        [Date].[Calendar].CurrentMember,
        [Date].[Calendar].[Date Key]
    )   
)

这在所有向下钻取情况下都可以正常使用，但是当我有过滤器时，不适用于总计.我怀疑CurrentMember在这种情况下不起作用.它总是返回我的数据中的总天数. 为了说明这一点，将具有以上公式的度量汇总到BIDS中，如下所示

This works fine for all drill-down situations, but it does not work for the Grand Total when I have a filter. I suspect that CurrentMember does not work in this situation. It always returns the total number of days in my data. To illustrate, the measure with the above formula is aggregated in BIDS as follows

因为我的事实数据始于1984年，所以时间维度上有11100天.如何更改公式，以便在聚合中考虑过滤器?用户可以深入到天"级别.这是Excel Pivot表:

because my fact data starts in 1984 and there are 11100 days in the time dimension. How can I change the formula so that the filter is accounted for in the aggregation? Users can drill down to the Day level. Here is the Excel Pivot table:

推荐答案

我认为这是带有计算成员的多选问题"的经典案例.我在此处找到了不错的摘要.我有一个可以在Excel中使用的解决方案.它无法在BIDS中计算出正确的总数，但是在这种情况下这不是问题.

I believe that this is a classic case of the "Multiselect Issue with Calculated Members". I found a good summary here. I have a solution that works in Excel. It does not calculate the proper totals in BIDS, but that's not an issue in this case.

我使用以下动态集(这是多维数据集编辑器中计算"选项卡上的脚本"视图):

I use the following dynamic set (this is the Script view on the Calculations tab in the cube editor):

CALCULATE;     

CREATE MEMBER CURRENTCUBE.[Measures].[Days In Period Count]
AS Count(Existing [Filtered Date]), VISIBLE = 1  ; 

CREATE DYNAMIC SET CURRENTCUBE.[Filtered Date]
AS [Date].[Calendar].[Date Key] ; 

我的解释是，SSAS通常会预先计算总量，并且 [Date].[Calendar] .CurrentMember在用户的向下钻取和过滤器操作的上下文之外的总计"级别没有意义.通过使用动态集，我迫使SSAS动态地计算聚合.从理论上讲，性能是不错的选择，但是我的立方体尺寸并没有引起太大的差别.

My interpretation is that SSAS normally calculates the aggregate in advance and that [Date].[Calendar].CurrentMember is not meaningful at the Grand Total level outside of the context of a user's drill-down and filter action. By using a dynamic set, I am forcing SSAS to calculate the aggregate dynamically. There is a theoretical performance hit, but with my cube size I did not notice much of a difference.

然后BIDS显示以下总计-上面没有更改:

Then BIDS shows the following totals - no change from above yet:

但是Excel的总计正确，仅反映了过滤后的日期坐标(在这种情况下，仅反映了2012年第一季度和2013年全年):

But Excel has the correct totals that reflect only the filtered date coordinates (in this case, only Q1 of 2012 and the entire year of 2013):

这篇关于计算时间维度节点中的天数-总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！