如何在MDX中创建一个按期计量? [英] How to create a period over period Measurement in MDX?

问题描述

我对MDX查询还很陌生，并且在理解如何计算周期测量值时遇到了麻烦.例如，说我有一个类似于收入的指标，并且我会逐月细分该收入.我将如何计算该收入在一个月到一个月之间的变化(百分比)?现在让我们说我想一般地计算任何季度，季度到季度，年份到年份，月份到月份的时间，或者甚至将其与前几年相同的时间段进行比较. 2011年第一季度与2012年第一季度，2011年第二季度与2012年第二季度，等等.

I'm fairly new to MDX queries, and I'm having trouble understanding how I might compute a period over period measurement. For example say I have a metric like revenue, and I'm breaking that revenue out over time say on a Month to Month basis. How would I calculate the change from Month to Month of that revenue as a percent? Now let's say I want to calculate that generically over any period, quarter to quarter, year to year, month to month, or even compare it with identical periods from prior years. 2011 Q1 vs 2012 Q1, 2011 Q2 vs 2012 Q2, etc.

模式定义:

<Dimension type="TimeDimension" highCardinality="false" name="Time">
    <Hierarchy name="yearQuarterMonth" caption="Year/Quarter/Month" hasAll="true" primaryKey="id">
        <Table name="Time"/>
        <Level name="Year" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"/>
        <Level name="Quarter" column="quarter" type="Numeric" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never"/>
        <Level name="Month" column="month" type="Numeric" ordinalColumn="month" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
        <Level name="Day" column="day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"/>
    </Hierarchy>
    <Hierarchy name="fiscalYearQuarterMonth" caption="Fiscal Year/Quarter/Month" hasAll="true" primaryKey="id">
        <Table name="Time"/>
        <Level name="Fiscal Year" column="fiscal_year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"/>
        <Level name="Quarter" column="quarter" type="Numeric" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never"/>
        <Level name="Month" column="month" type="Numeric" ordinalColumn="month" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
        <Level name="Day" column="day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"/>
    </Hierarchy>
    <Hierarchy name="yearMonth" caption="Year/Month" hasAll="true" primaryKey="id">
        <Table name="Time"/>
        <Level name="Year" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"/>
        <Level name="Month" column="month" type="Numeric" ordinalColumn="month" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
        <Level name="Day" column="day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"/>
    </Hierarchy>
    <Hierarchy name="fiscalYearMonth" caption="Fiscal Year/Month" hasAll="true" primaryKey="id">
        <Table name="Time"/>
        <Level name="Fiscal Year" column="fiscal_year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"/>
        <Level name="Month" column="month" type="Numeric" ordinalColumn="month" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
        <Level name="Day" column="day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"/>
    </Hierarchy>
</Dimension>

推荐答案

对于Period of Period计算，您将需要使用PrevMember函数进行研究... http://msdn.microsoft.com/en-us/library/ms144719.aspx

For Period over Period calculations, you will want to look into using the PrevMember function... http://msdn.microsoft.com/en-us/library/ms144719.aspx

下面是一个片段，应该可以让您在"Period over Period"方面有个良好的开端...

Below is a snippet that should give you a good start on Period over Period...

WITH
MEMBER [Measures].[WO Actual Amount PP] AS
    (
         [Order Date].[Calendar].CurrentMember.PrevMember
        ,[Measures].[WO Actual Amount]
    )
    ,FORMAT_STRING = "Currency"
MEMBER [Measures].[Prior Period Growth %] AS
    IIF(
         [Measures].[WO Actual Amount PP] = 0
        ,'N/A'
        ,([Measures].[WO Actual Amount]-[Measures].[WO Actual Amount PP])/[Measures].[WO Actual Amount PP]
    )
    ,FORMAT_STRING = "Percent"
SELECT
    NON EMPTY {
        [Measures].[WO Actual Amount],
        [Measures].[WO Actual Amount PP],
        [Measures].[Prior Period Growth %]
    } ON 0,
    NON EMPTY {
        [Order Date].[Calendar Year].[Calendar Year].Members
    } ON 1
FROM
    [<<cube name>>]

对于上一年同期或计算，您需要查看ParallelPeriod函数... http://msdn.microsoft.com/en-us/library/ms145500.aspx

For Same Period Prior Year or calculation, you'll want to look into the ParallelPeriod function... http://msdn.microsoft.com/en-us/library/ms145500.aspx

下面是一个片段，应该可以让您在年度复活中有个不错的开端...

Below is a snippet that should give you a good start on Year over Year...

WITH
MEMBER [Measures].[WO Actual Amount YoY] AS
    (
         ParallelPeriod(
              [Order Date].[Calendar].[Calendar Year]
             ,1
             ,[Order Date].[Calendar].CurrentMember
         )
        ,[Measures].[WO Actual Amount]
    )
    ,FORMAT_STRING = "Currency"
MEMBER [Measures].[Prior Period Growth %] AS
    IIF(
         [Measures].[WO Actual Amount YoY] = 0
        ,'N/A'
        ,([Measures].[WO Actual Amount]-[Measures].[WO Actual Amount YoY])/[Measures].[WO Actual Amount YoY]
    )
    ,FORMAT_STRING = "Percent"
SELECT
    NON EMPTY {
        [Measures].[WO Actual Amount],
        [Measures].[WO Actual Amount YoY],
        [Measures].[Prior Period Growth %]
    } ON 0,
    NON EMPTY {
        [Order Date].[Calendar Month].[Calendar Month].Members
    } ON 1
FROM
    [<<cube name>>]

这篇关于如何在MDX中创建一个按期计量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！