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

Answers

  • Hi Crozby,

    Thanks for your response.

    In this scenario, please try following MDX query:

    WITH MEMBER [Measures].[AvgCost] AS 
    AVG(DESCENDANTS([Date].[Calendar Date].CURRENTMEMBER, [Date].[Calendar Date].[Date]), 
    [Measures].[Cost])
    SELECT [Measures].[AvgCost] ON COLUMNS, 
    NON EMPTY {[Date].[Calendar Date].[Calendar Month].MEMBERS} ON ROWS 
    FROM [Table]
    Where
    ([Date].[Calendar Year].&[2000]:[Date].[Calendar Year].&[2003])

    WITH MEMBER [Measures].[AvgCost] AS 
    AVG(DESCENDANTS([Date].[Calendar Date].CURRENTMEMBER, [Date].[Calendar Date].[Date]), 
    [Measures].[Cost])
    SELECT [Measures].[AvgCost] ON COLUMNS, 
    NON EMPTY {[Date].[Calendar Date].[Calendar Month].MEMBERS} ON ROWS 
    FROM [Table]
    Where
    ([Date].[Calendar Year].&[2004]:[Date].[Calendar Year].&[2006])

    WITH MEMBER [Measures].[AvgCost] AS 
    AVG(DESCENDANTS([Date].[Calendar Date].CURRENTMEMBER, [Date].[Calendar Date].[Date]), 
    [Measures].[Cost])
    SELECT [Measures].[AvgCost] ON COLUMNS, 
    NON EMPTY {[Date].[Calendar Date].[Calendar Month].MEMBERS} ON ROWS 
    FROM [Table]
    Where
    ([Date].[Calendar Year].&[2007]:[Date].[Calendar Year].&[2010])

    See my sample MDX query below:
    WITH MEMBER [Measures].[Avg Sales Amount] AS 
    Avg(DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Date]), 
    [Measures].[Sales Amount])
    Select {[Measures].[Sales Amount],
    [Measures].[Avg Sales Amount]} on 0,
    [Date].[Calendar].[Month].members on 1
    From 
    [Adventure Works]
    where ([Date].[Calendar Year].&[2011]:[Date].[Calendar Year].&[2013])


    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 Thursday, March 30, 2017 10:20 PM
    • Marked as answer by Crozby Tuesday, April 18, 2017 12:03 AM
    Thursday, March 30, 2017 9:17 AM

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
  • Hi

    Firstly, thank you everyone who is making time to help me.

    I confess my problem is I basically don't get how to apply a set of arbitrary date ranges for an operation. I am quite new to MDX, I've mostly worked with MDX that was generated by SSRS, and I am struggling to 'get it'.

    I understand how this statement works:

    WITH MEMBER [Measures].[AvgCost] AS AVG(DESCENDANTS([Date].[Calendar Date].CURRENTMEMBER, [Date].[Calendar Date].[Date]), [Measures].[Cost])

    SELECT [Measures].[AvgCost] ON COLUMNS, NON EMPTY {[Date].[Calendar Date].[Calendar Month].MEMBERS} ON ROWS FROM [Table]

    but I don't understand how to get the average costs for a set of arbitrary date ranges, eg 2000-2003, 2004-2006, 2007-2010.

    Specifically, I don't understand how to (a) create sets of dates, each containing 3 years of dates, and (b) output an average cost for each of those sets.

    There is something about MDX that is not clicking for me. I can't seem to work this out for myself despite googling a gazzilion examples. If I can just get this to click I am hoping a lot of other stuff will fall into place.


    Thursday, March 30, 2017 12:58 AM
  • The one confusing point about sets in MDX is that there are sets of members and sets of tuples. Sometimes I wish they'd come up with separate names for these two concepts...

    Threre are several ways to construct a set with MDX. For consecutive members of an attribute, dates being a textbook example, the range operator, semicolon, is probably the most obvious. The syntax is First_Member:Last_Member, simple as that, and the result is of course a set of members.

    Average value over a set is just AVG(set, value), where value is typically a measure.

    Averages over several arbitrary sets is the first non-trivial task, the first question being just how do the users input the desired date ranges for all of the ranges. With SSRS, however, you don't even have to construct just a single MDX query to return all the averages and corresponding headings.


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

    Thursday, March 30, 2017 8:06 AM
  • Hi Crozby,

    Thanks for your response.

    In this scenario, please try following MDX query:

    WITH MEMBER [Measures].[AvgCost] AS 
    AVG(DESCENDANTS([Date].[Calendar Date].CURRENTMEMBER, [Date].[Calendar Date].[Date]), 
    [Measures].[Cost])
    SELECT [Measures].[AvgCost] ON COLUMNS, 
    NON EMPTY {[Date].[Calendar Date].[Calendar Month].MEMBERS} ON ROWS 
    FROM [Table]
    Where
    ([Date].[Calendar Year].&[2000]:[Date].[Calendar Year].&[2003])

    WITH MEMBER [Measures].[AvgCost] AS 
    AVG(DESCENDANTS([Date].[Calendar Date].CURRENTMEMBER, [Date].[Calendar Date].[Date]), 
    [Measures].[Cost])
    SELECT [Measures].[AvgCost] ON COLUMNS, 
    NON EMPTY {[Date].[Calendar Date].[Calendar Month].MEMBERS} ON ROWS 
    FROM [Table]
    Where
    ([Date].[Calendar Year].&[2004]:[Date].[Calendar Year].&[2006])

    WITH MEMBER [Measures].[AvgCost] AS 
    AVG(DESCENDANTS([Date].[Calendar Date].CURRENTMEMBER, [Date].[Calendar Date].[Date]), 
    [Measures].[Cost])
    SELECT [Measures].[AvgCost] ON COLUMNS, 
    NON EMPTY {[Date].[Calendar Date].[Calendar Month].MEMBERS} ON ROWS 
    FROM [Table]
    Where
    ([Date].[Calendar Year].&[2007]:[Date].[Calendar Year].&[2010])

    See my sample MDX query below:
    WITH MEMBER [Measures].[Avg Sales Amount] AS 
    Avg(DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Date]), 
    [Measures].[Sales Amount])
    Select {[Measures].[Sales Amount],
    [Measures].[Avg Sales Amount]} on 0,
    [Date].[Calendar].[Month].members on 1
    From 
    [Adventure Works]
    where ([Date].[Calendar Year].&[2011]:[Date].[Calendar Year].&[2013])


    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 Thursday, March 30, 2017 10:20 PM
    • Marked as answer by Crozby Tuesday, April 18, 2017 12:03 AM
    Thursday, March 30, 2017 9:17 AM
  • I hadn't considered doing it with multiple queries. I think that gets me what I need.

    Thank you and Willson for helping.

    Thursday, March 30, 2017 9:17 PM
  • Thank you Willson that's very helpful.
    Thursday, March 30, 2017 9:17 PM
  • Hi Crozby,

    Thanks for your response.

    If my reply is helpful to you, please kindly mark it as an answer. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated. 

    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

    Friday, March 31, 2017 1:00 AM