none
Aggregating over greater than a year, and over arbitrary date ranges

    Question

  • Hi

    I have a requirement to report aggregated measures (a) over periods longer than a year, i.e. 5 years, 10 years, 20 years, and (b) aggregate measures over arbitrary date ranges, for example 2016-01-03 - 2017-01-03.

    My cube has calculated values, for example standard deviations, that cannot be simply aggregated: stdev(2016-2017) <> stdev(2016) + stdev(2017).

    How best should I implement my requirements?

    My thoughts are to add decades and 5year groups to my date dimension, and to use SSRS to perform the calculations for arbitrary date ranges. Is there a better way to do either?


    Monday, March 20, 2017 10:53 PM

All replies

  • Hi C,

    You just need to write your calculation to work over the set. Stdev() has the following syntax

    Stdev({Set expression}, numeric expression)

    So, in your set expression, you will want to ensure that the appropriate population is there. You will probably use the * (crossjoin), generate(), descendants() function to help you with this.

    Hope that helps,


    Richard

    Monday, March 20, 2017 11:00 PM
  • Thanks for helping Richard.

    So I have a calculation in by Cube that is like this:

    STDEV(DESCENDANTS([Date].[Calendar Date].CurrentMember, [Date].[Calendar Date].Levels.Count - 1, SELF), [Measures].[Profit])

    which gives me a STD for the levels in my date hierarchy.

    I'm not clear how I should implement what you suggest. To additionally calculate STDEVs for years 2000-2005, 2005-2010, 2010-2015, 2016- , what would I cross join the above set with?

    I'm also not clear how I would access the new stdevs from excel.

    Monday, March 20, 2017 11:58 PM
  • Hi Crozby,

    Thanks for your question.

    You may try following MDX expression:

    Create Member CurrentCube.[Measures].[StdDev] As
    iif(IsEmpty(Axis(0).Item(0)), NULL,
    StdDev(Axis(1).Item(0).Item(0).Dimension.Children,
    Axis(0).Item(0)));



    See my sample MDX query below:

    With Member [Measures].[ChildStdDev] as
    iif(IsEmpty(Axis(0).Item(0)), NULL,
    StdDevP(Axis(1).Item(0).Item(0).Dimension.Children,
    Axis(0).Item(0)))
    select {[Measures].[Internet Sales Amount], [Measures].[ChildStdDev]} on 0,
    Non Empty ([Date].[Calendar].[Calendar Year].&[2011]:[Date].[Calendar].[Calendar Year].&[2013]) on 1
    from [Adventure Works]

    With Member [Measures].[ChildStdDev] as
    iif(IsEmpty(Axis(0).Item(0)), NULL,
    StdDevP(Axis(1).Item(0).Item(0).Dimension.Children,
    Axis(0).Item(0)))
    select {[Measures].[Internet Sales Amount], [Measures].[ChildStdDev]} on 0,
    Non Empty ([Date].[Calendar].[Month].&[2011]&[9]:[Date].[Calendar].[Month].&[2012]&[5]) on 1
    from [Adventure Works]

    For more information, you can also refer to this similar thread Standard deviation calculation.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Tuesday, March 21, 2017 9:31 AM
    Tuesday, March 21, 2017 6:33 AM
  • As Richard said, you'll just have to adjust your calculations' MDX to accommodate the new requirements.

    Taking your STDEV as an example, it is calculated over a rather simple set of leaf members of the Calendar Date hierarchy. With a set of years or an arbitrary range to calculate over, you can no longer utilize Descendants ( CurrentMember ) to define the set. Since you mentioned SSRS, you are probably using SSRS parameters to obtain users' input, and you are also in total control over MDX. One of the options to meet both your requirement would be to ask for StartDate and EndDate as paramenters (assuming day-level grain) and then use

    STDEV ( @StartDate:@EndDate , [Measures].[Profit] )

    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, March 21, 2017 9:10 AM