计算Analysis Services中的四分位数

问题描述

我正在使用MDX代码来计算四分位数，就像在此博客中一样: https://electrovoid.wordpress.com/2011/06/24/ssas-四分位/

I´m using MDX code to calculate quartile, , like in this blog:https://electrovoid.wordpress.com/2011/06/24/ssas-quartile/

这就是我在做什么:

WITH SET OrderedData AS 
ORDER
    (    
        NONEMPTY
                (
                 [Dim Parameter].[id].[id]
                 *[Dim Result].[Id].[Id].ALLMEMBERS,
                 [Measures].[Value]
                ),  
        [Measures].[Value], 
        BASC
     )

MEMBER [Measures].[RowCount] AS COUNT (OrderedData)
MEMBER [Measures].[i25] AS ( .25 *  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i25Lo] AS FIX([i25])   - 1
MEMBER [Measures].[i25Rem] AS ([i25] - FIX([i25]))
MEMBER [Measures].[n25Lo] AS (OrderedData.Item([i25Lo]), [Value])
MEMBER [Measures].[n25Hi] AS (OrderedData.Item([i25Lo] + 1), [Value])
MEMBER [Measures].[Quartile1] AS [n25Lo] + ( [i25Rem] * ( [n25Hi] - [n25Lo] ))
,FORMAT_STRING='Currency'
MEMBER [Measures].[Quartile2] AS MEDIAN(OrderedData, [Value])
,FORMAT_STRING='Currency'
MEMBER [Measures].[i75] AS ( .75 *  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i75Lo] AS FIX([i75]) - 1
MEMBER [Measures].[i75Rem] AS ([i75] - FIX([i75]))
MEMBER [Measures].[n75Lo] AS (OrderedData.Item([i75Lo] ),[Value])
MEMBER [Measures].[n75Hi] AS (OrderedData.Item([i75Lo] + 1),[Value])
MEMBER [Measures].[Quartile3] AS [n75Lo] + ( [i75Rem] * ( [n75Hi] - [n75Lo] ))
,FORMAT_STRING='Currency'
MEMBER [Measures].[RIC] As ([Quartile3]-[Quartile1] )
MEMBER [Measures].[Ls] As ([Quartile3]+ ([RIC]*1.5) )
MEMBER [Measures].[Li] As ([Quartile1]- ([RIC] *1.5)) 
MEMBER [Measures].[MAX] as  MAX (Filter(OrderedData ,[value]<=[LS]),[value])
MEMBER [Measures].[Min] as  MIn(Filter(OrderedData ,[value]>=[Li]),[value])
MEMBER [Measures].[out] as  MAX (Filter(OrderedData ,[value]>[LS]),[value

我想要的是添加Dim日期，以计算每个月的四分位数，如下所示:

What I want is to add Dim date, to calculate the quartiles for each month, something like this:

MEMBER [Measures].[out] as MAX (Filter(OrderedData ,[value]>[LS]),[value

SELECT {
        [Measures].[Quartile1],[Measures].[Quartile2],[Measures].[Quartile3], [min],

        [MAX] , [out] , [Measures].[ValueAVG],[RowCount],[Measures].[Recuento Fact Result]
       } ON 0 , 
[Dim Parameter].[Reference].[Reference] * 
[Dim Parameter].[Section ES].[Section ES] * 
[Id Distribution Date].[DateJ].[Month] ON 1 
FROM [Tess Tek DW Dev]

但是它没有用，我如何只用一个mdx查询就可以计算不同日期范围的四分位数?

But it didn't work, How i can calculate quartiles of different date ranges in only one mdx query?

推荐答案

您需要以某种方式将Date放入您的WITH语句中.

You need to work the Date into your WITH statement somehow.

尝试首先将目标行(将在行中)添加到命名集中:

Try first adding your target months, that will be on the rows, into a named set:

WITH 
SET [TargetSet] AS
 {
  [Id Distribution Date].[DateJ].[Month].[Jan-2015],
  [Id Distribution Date].[DateJ].[Month].[Feb-2015]
 }

然后我将考虑到TargetSet的另一个集合添加进来:

Then I'd add another set taking TargetSet into account:

SET [NonEmptyIds] AS
 NonEmpty(
    [Dim Parameter].[id].[id]
   *[Dim Result].[Id].[Id].ALLMEMBERS
  ,
  {[Measures].[Value]} * [TargetSet]
 )

然后将此集合供入您的当前集合:

Then feed this set into your current set:

SET [OrderedData] AS 
ORDER
    (    
        [NonEmptyIds],  
        [Measures].[Value], 
        BASC
     )

然后尝试修改行代码片段以使用TargetSet:

Then try amending the rows snippet to use the TargetSet:

[Dim Parameter].[Reference].[Reference] * 
[Dim Parameter].[Section ES].[Section ES] * 
[TargetSet] ON 1  

这篇关于计算Analysis Services中的四分位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！