none
LastChild aggregation function in ssas RRS feed

  • Question

  • Hello All,
    I'm using TIME dimension with months, quarters and years as levels in time dimension hierarchy.
    Now I want to get the LASTMONTH total as aggregation for quarter.

    i.e. each QUARTER should return respective last month totals instead of sum-of-three-months and similarly YEAR should return last month total

    My calender starts from OCT (1st month) to SEP (last month) and I DONT use individual DAYs in my table, i use only months,quarters and years.

    I tried with LASTCHILD aggregation function for MEASURE, but it is providing some RANDOM month total instead of LAST month.
    I tried with SCOPE, but unfortunately it is REALLY static, for e.g.
    [Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2002].LastChild

    like mentioned I must use each quarter member name. I tried even with [Date].[Fiscal].[Fiscal Quarter].CurrentMember, [Date].[Fiscal].[Fiscal Quarter].[ALL].CurrentMember etc without success

    Can anybody tell me how I achieve this

    Thanks a lot
    Swamy

    Friday, February 17, 2012 9:52 PM

Answers

  • Here is the Adventure work query that gives the Internet sales amount of the last month of the selected quarter -

    WITH MEMBER LastMonthTotal AS 
    (tail(DESCENDANTS(ANCESTOR([Date].[Calendar].currentmember,
    [Date].[Calendar].[Calendar Quarter]),[Date].[Calendar].[Month]),1).item(0),
    Measures.[Internet Sales Amount])
    SELECT {Measures.[Internet Sales Amount],Measures.[LastMonthTotal]} On 0,
    drilldownlevel({[Date].[Calendar].[Calendar Quarter].members}) ON 1 
    FROM [Adventure Works]
    

    Here is the result -

    Thanks,

    P


    Hope this Helps!

    • Marked as answer by MS World Fan Saturday, February 18, 2012 11:27 AM
    Friday, February 17, 2012 11:14 PM

All replies

  • Here is the Adventure work query that gives the Internet sales amount of the last month of the selected quarter -

    WITH MEMBER LastMonthTotal AS 
    (tail(DESCENDANTS(ANCESTOR([Date].[Calendar].currentmember,
    [Date].[Calendar].[Calendar Quarter]),[Date].[Calendar].[Month]),1).item(0),
    Measures.[Internet Sales Amount])
    SELECT {Measures.[Internet Sales Amount],Measures.[LastMonthTotal]} On 0,
    drilldownlevel({[Date].[Calendar].[Calendar Quarter].members}) ON 1 
    FROM [Adventure Works]
    

    Here is the result -

    Thanks,

    P


    Hope this Helps!

    • Marked as answer by MS World Fan Saturday, February 18, 2012 11:27 AM
    Friday, February 17, 2012 11:14 PM
  • Hi P alias SSAS Beginner ;)

    Thanks a lot, just perfect...

    Swamy

    Saturday, February 18, 2012 11:27 AM