none
Year on year growth but only for active records RRS feed

  • Question

  • Greetings from the UK hopefully someone can help me with my MDX as I am not as good as I need to be.

    I am looking  acube of data for a set of houses and need to calculate % year on year growth in charge for the base of units that where in scope at the start of the measurement and are still live at the end.

    I have :

    1.     A measure [Measures].[Annual Charge]

    2..    A Calculated member of charge for last year  [AnnualChargePreviousYear]

    sum({ParallelPeriod([Time].[Calendar].[English Month Name], 12,

    [Time].[Calendar].CurrentMember)},[Measures].[Annual Charge])

    3. A Change in totals year on year calculated member [AnnualChargeChangeYear]

    Case

    When IsEmpty ([Measures].[Annual Charge] ) Then Null

    When IsEmpty ([Measures].[AnnualChargePreviousYear] ) Then Null

    When ([Measures].[Annual Charge]) = 0 Then null

    When ([Measures].[AnnualChargePreviousYear]) = 0 Then null

    Else ([Measures].[Annual Charge]-[Measures].[AnnualChargePreviousYear])/[Measures].[AnnualChargePreviousYear]          

    When I calculate the % change from 3 I get the following which is correct  as a change in the Total of the annual charge

    English Month Name

    [AnnualChargePreviousYear]

    [Annual Charge]

    [AnnualChargeChangeYear]

    June 2012

    526,971

    549,023

    4.18%

    July 2012

    521,199

    552,353

    5.98%

    August 2012

    527,463

    554,489

    5.12%

    September 2012

    537,133

    556,397

    3.59%

    October 2012

    544,967

    550,097

    0.94%

    November 2012

    546,167

    536,973

    -1.68%

    December 2012

    547,805

    537,723

    -1.84%

    January 2013

    551,505

    538,083

    -2.43%

    February 2013

    549,253

    556,001

    1.23%

    March 2013

    541,777

    543,619

    0.34%

    April 2013

    548,951

    544,485

    -0.81%

    May 2013

    548,951

    539,149

    -1.79%

    June 2013

    549,023

    533,779

    -2.78%

    But what I need is the change only where the unit record was there in the previous year and is still active in the current period.

    And am unsure if I should use  “nonempty”, “Exists” or another function:

    To get the Year on Year movement for active records, using this post

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2de9dae2-e8cc-4d0d-b01b-56fc449a508a/how-to-add-a-calculated-member-growth-year-over-year-on-only-the-members-that-are-in-both-the

    I have tried:

    with MEMBER [Measures].[yoy] AS

    SUM(

    NonEmpty(NonEmpty([Property Details].[PropertyID].[PropertyID].MEMBERS, [Measures].[Annual Charge])

    , ([Time].[Calendar Year].PrevMember, [Measures].[Annual Charge]))

    ,  ([Time].[Calendar Year].CurrentMember, ([Measures].[Annual Charge])

    -([Time].[Calendar Year].PrevMember,[Measures].[Annual Charge])))

    Where I am trying to find only those records where the property ID was  present a year ago then doing just the charge change calculation but to no avail as this sums to a year when I need the annual change but by month, replicating the results table above but only for properties live at both the start and end

    Any ideas gratfully received. Apologies if this is unclear but I am really stuck with this.

    Regards

    Kevin


    Still bad at golf but trying to get better and now officailly rubbish at playing guitar...


    Monday, July 1, 2013 12:59 PM

All replies

  • Hi Gridhead,

    Thank you for your post. I am currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding.

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, July 3, 2013 9:41 AM
    Moderator

  • Hi Kevin,

    Please try this script, I used two calculated measures:

    with MEMBER [Measures].[prevYear] AS
    (
     [Measures].[Annual Charge],
     ParallelPeriod  (
      [Time].[Calendar Year].[Year],   -- year might be called different in your cube
      1,
      [Time].[Calendar Year].CurrentMember
     )
    )

    Member [Measures].[YoY] AS
    (
     IIF (
      [Measures].[prevYear]=0,
      NULL,
       -- here you could also use a calculation to have this inpercentage, or use an IIF to check [Measures].[Annual Charge] too
      ([Measures].[Annual Charge]- [Measures].[prevYear])  
     )
    )

    Also check out this description from Chris Webb
    http://wiki.pentaho.com/display/COM/MDX+Calculations+-+Previous+Period+Growth


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thanks,
    Orsi
    Microsoft Online Community Support



    Tuesday, July 16, 2013 2:39 PM
    Answerer