MDX查询:如何比较当前年度的年初至今数据和上一年的年度至今数据? [英] MDX query: How to compare year to date figures from the current year with year to date figures from the previous year?

问题描述

在下面的MDX查询中，我试图返回销售数字的比较.

With the below MDX queries I am attempting to return a comparison of sales figures.

我想将YTD与LYTD进行比较，定义如下:

I want to compare YTD with LYTD, defined as follows:

年初至今:今年1月1日(2011年1月1日)直到今天(2011年8月17日)

YTD: 01 Jan this year (01/Jan/2011) until today (17/Aug/2011)

LYTD:去年1月1日(2010年1月1日)到去年的今天(2010年8月17日)

LYTD: 01 Jan last year (01/Jan/2010) until today's date last year (17/Aug/2010)

两个时期应包含相同的天数((年为1天或为1天).

Both periods should contain the same number of days (give or take one day for leap years).

到目前为止，我已经尝试使用YTD()函数获取今年的数据，但是在将YTD()函数包装到某种东西以使其返回与去年相同的日期方面并没有取得太大的成功.我已经使用了LAG(1)和ParallelPeriod(请参见下面的两个示例):

So far I have tried using the YTD() function to get this year's data but I have not had much success with wrapping the YTD() function in something to get it to return the same dates from last year. I have employed LAG(1) and ParallelPeriod (see both examples below):

WITH
MEMBER [Measures].[Sales YTD] AS
    'SUM(YTD(),[Measures].[Sales])'
MEMBER [Measures].[Sales LYTD] AS
    'SUM((YTD(),[Measures].[Sales]).LAG(1))'
SELECT
    {[Sales YTD], [Sales LYTD]} ON COLUMNS
    ...

或

WITH
MEMBER [Measures].[Sales YTD] AS
    'SUM(YTD(),[Measures].[Sales])'
MEMBER [Measures].[Sales LYTD] AS
    'SUM(ParallelPeriod(YTD(),1),[Measures].[Sales])'
SELECT
    {[Sales YTD], [Sales LYTD]} ON COLUMNS
    ...

使用ParallelPeriod或LAG时产生的数据始终返回去年的销售额为空的列:

The resulting data when using either ParallelPeriod or LAG always returns a column of nulls for Last Year's sales:

Customer    Sales YTD  Sales LYTD
ABC Screws      $1024       (Null)
ABC Bolts       $2392       (Null)
ABC Nuts        $3735       (Null)

有人可以建议对查询进行更改以返回正确的数据，或者为使用LAG或ParallelPeriod提供替代策略吗?

Can anybody suggest changes to the query which will return the correct data, or perhaps offer an alternative strategy to using LAG or ParallelPeriod?

推荐答案

使用类似的内容:

WITH
        MEMBER [Measures].[Sales YTD] AS
            'SUM(YTD(closingperiod([Date].[Calendar].[Date])),[Measures].[Sales])'
        MEMBER [Measures].[Sales LYTD] AS
            'SUM(YTD(PARALLELPERIOD([Date].[Calendar].[Calendar Year],1,closingperiod([Date].[Calendar].[Date]))),[Measures].[Sales])'
        SELECT
            {[Measures].[Sales YTD],[Measures].[Sales LYTD]} ON COLUMNS
        ...

，其中

• [Date].[Calendar].[Date]-日期"维度中的天"级别；
• closingperiod([Date].[Calendar].[Date])-此级别天"的最后一个成员(17/Aug/2011);
• [Date].[Calendar].[Calendar Year]-日期级别为"Year"；
• PARALLELPERIOD([Date].[Calendar].[Calendar Year]，1，closingperiod([Date].[Calendar].[Date]))-17/Aug/2010.

这篇关于MDX查询:如何比较当前年度的年初至今数据和上一年的年度至今数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！