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

来源：互联网发布：网络终端机改造编辑：程序博客网时间：2024/05/16 15:08

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

1 同期与同比

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

withmember [同期] 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 1from         [Park]where         [Date].[Year Num].&[2016]

2 上期与环比

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

withmember [上期] 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 1from         [Park]where         [Date].[Year Num].&[2016]

3 滚动累加与滚动平均值

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

withmember [滚动累加值] 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 1from         [Park]where         [Date].[Year Num].&[2016]

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

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

withmember [年内累加值] 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 1from         [Park]where         [Date].[Year Num].&[2016]

0 0