6.15、同环比、累加、累加平均、滚动累加、滚动平均

同环比、滚动累加、滚动平均

 

1          同期与同比

使用cousin和parallPeriod函数都可以获取到同期的成员，但是一般用cousin因为使用起来比较简单，示例

with
member [同期] as
         (
                   cousin(
                            [Date].[年-月-日].currentMember,
                            [Date].[年-月-日].currentMember.parent.prevMember
                   ),
                   [Measures].[In Count]
         )
 
member [同比] as
         ([Date].[年-月-日].currentMember,[Measures].[In Count])/[同期] - 1
,format="percent"
 
select
         {
                   [Measures].[In Count],
                   [同期],
                   [同比]
         }on 0,
         [Date].[年-月-日].[Month NumOf Year].members on 1
from
         [Park]
where
         [Date].[Year Num].&[2016]

 

2          上期与环比

可使用prevMember访问到上期的成员

with
member [上期] as
         ([Date].[年-月-日].currentMember.prevMember,[Measures].[In Count])
 
member [环比] as
         ([Date].[年-月-日].currentMember,[Measures].[In Count])/[上期] - 1
,format="percent"
 
select
         {
                   [Measures].[In Count],
                   [上期],
                   [环比]
         }on 0,
         [Date].[年-月-日].[Month NumOf Year].members on 1
from
         [Park]
where
         [Date].[Year Num].&[2016]

 

3          滚动累加与滚动平均值

计算前推两个月的滚动累加值和滚动平均值，例：

with
member [滚动累加值] as
         aggregate(
                   lastPeriods(
                            3,
                            [Date].[年-月-日].currentMember
                   ),
                   [Measures].[In Count]
         )
 
member [滚动平均值] as
         avg(
                   lastPeriods(
                            3,
                            [Date].[年-月-日].currentMember
                   ),
                   [Measures].[In Count]
         )
 
select
         {
                   [Measures].[In Count],
                   [滚动累加值],
                   [滚动平均值]
         }on 0,
         (
                   [Date].[年-月-日].[Month NumOf Year].members
         )on 1
from
         [Park]
where
         [Date].[Year Num].&[2016]

 

4          指定某一层级的累加值与累加平均值

计算从某一层级开始的累加值和累加平均值，比如2016年1月起各月的累加值和累加平均值

with
member [年内累加值] as
         aggregate(
                   periodsToDate(
                            [Date].[年-月-日].[YearNum],
                            [Date].[年-月-日].currentMember
                   ),
                   [Measures].[In Count]
         )
 
member [年内累加平均值] as
         avg(
                   periodsToDate(
                            [Date].[年-月-日].[YearNum],
                            [Date].[年-月-日].currentMember
                   ),
                   [Measures].[In Count]
         )
 
select
         {
                   [Measures].[In Count],
                   [年内累加值],
                   [年内累加平均值]
         }on 0,
         (
                   [Date].[年-月-日].[Month NumOf Year].members
         )on 1
from
         [Park]
where
         [Date].[Year Num].&[2016]