Post reply

Using a SET in a Previous Year Calculation

  • February 23, 2016 at 5:04 am

    #151453

    Hi everyone

    My MDX skills aren't the greatest so I'm battling with the following query:

    WITH

    SET [ATL] AS {

    [Income Statement].[Income Statement Lines].[IS Level4].&[71],

    [Income Statement].[Income Statement Lines].[IS Level4].&[74]}

    MEMBER [Measures].[ATL] AS

    SUM((PeriodsToDate([Date].[Financial Dates].[Financial Year], [Date].[Financial Dates].CURRENTMEMBER), ([ATL], [Measures].[Value])))

    MEMBER [Measures].[PYATL] AS

    SUM(PeriodsToDate([Date].[Financial Dates].[Financial Year], ParallelPeriod([Date].[Financial Dates].[Financial Year], 1, [Date].[Financial Dates].CURRENTMEMBER)), ([ATL], [Measures].[Value]))

    .

    .

    .

    .

    My first member returns the correct total (ATL), but my second one (PYATL) returns "#Error" because I am using a tuple set expression.

    The problem is that I can't figure out how to structure the syntax of PYATL so that it'll return the Previous Year's value.

    Thanks for your time.

    Regards

    Paulo

  • February 23, 2016 at 10:24 pm

    #1860621

    I found the problem.

    I wasn't combining PeriodsToDate with ATL into a single SET within the SUM function.

    The second SUM line should be as follows:

    SUM((PeriodsToDate([Date].[Financial Dates].[Financial Year], ParallelPeriod([Date].[Financial Dates].[Financial Year], 1, [Date].[Financial Dates].CURRENTMEMBER)), [ATL]), [Measures].[Value])

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply