SSAS MDX 上一年 - 忽略过滤器 [英] SSAS MDX Previous Year - Ignore Filter

问题描述

我尝试获得实际营业额和上一年同期的营业额.

i try to get actual turnover and the turnover of the previous year in the same period.

我写了这个查询:

with 
member [Measures].[Turnover PrevYear] as 
IIF( IsEmpty([Measures].[Turnover Actual] ), 
    NULL,   
    SUM(      
       (ParallelPeriod( [Date].[Year - Quarter - Month - Date].[Year],1,[Date].[Year - Quarter - Month - Date].CurrentMember))
       ,   [Measures].[Turnover Actual]
       ) 
    )
Select  
non empty{[Measures].[Turnover Actual],[Measures].[Turnover PrevYear]} 
on Columns,
non empty{[Store].[Store].[Store].members}
on Rows
from [Sales Cube]
where (
[Date].[Year - Quarter - Month - Date].[Month].&[2020]&[1], 
[Store Status].[Store Status Type].&[Comparable], 
[Store].[Country].[Country].&[GERMANY]
)

现在我得到以下结果:

商店 |营业额实际|上年营业额

Store | Turnover Actual | Turnover PrevYear

汉堡 |100.00 |120.00

Hamburg | 100.00 | 120.00

慕尼黑 |140.00 |130.00

Munich | 140.00 | 130.00

科隆 |90.00 |110.00

Cologne | 90.00 | 110.00

柏林 |150.00 |空

Berlin | 150.00 | null

柏林没有上一年的价值，因为商店在 2019 年 1 月的状态为不可比".此商店状态"过滤器不仅适用于实际营业额，也适用于上一年的计算.如何在计算中忽略此过滤器?无论去年的商店是不可比的"，我都想从前一年获得柏林的价值.此过滤器仅针对实际情况，将实际不可比较"的商店踢出此结果列表.

Berlin doesn't have a prevoius year value because the store had the status "Not Comparable" in january 2019. This "Store Status" Filter operate not only for the actual turnover also for the previous year calculation. How can i ignore this filter on the calculation? I wants to get a value for berlin from previous year no matter the store was "Not comparable" in the last year. This filter is only for the actual situation to kick the actual "Not comparable" Stores out of this result list.

推荐答案

您需要将默认成员"添加到要忽略上下文的任何计算中.您的代码将是

You need to add "default member" to any calculation that you want to ignore the context. Your code will be

with 
member [Measures].[Turnover PrevYear] as 
IIF( IsEmpty([Measures].[Turnover Actual] ), 
    NULL,   
    SUM(      
       ([Store Status].[Store Status Type].defaultmember, ParallelPeriod( [Date].[Year - Quarter - Month - Date].[Year],1,[Date].[Year - Quarter - Month - Date].CurrentMember))
       ,   [Measures].[Turnover Actual]
       ) 
    )
Select  
non empty{[Measures].[Turnover Actual],[Measures].[Turnover PrevYear]} 
on Columns,
non empty{[Store].[Store].[Store].members}
on Rows
from [Sales Cube]
where (
[Date].[Year - Quarter - Month - Date].[Month].&[2020]&[1], 
[Store Status].[Store Status Type].&[Comparable], 
[Store].[Country].[Country].&[GERMANY]
)

这篇关于SSAS MDX 上一年 - 忽略过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！