none
Date range Average

    Question

  • Hi

    I would like to create a member to calculate the average of the data selected in the users query.

    eg
    Date Jan Feb Mar Apr
    Measure 2 4 6 8
    Average 5 5 5 5

    if date selection changes, so will the average

    Date Jan Feb Mar Apr May
    Measure 2 4 6 8 10
    Average 6 6 6 6 6


    So within the script I want to reference the first and last date in the selection. Is this possible?

    I did solve this solution using 2 extra date dimensions (from and to), but ths is too much work for the users to deal with (yeah I know...),
    Here is how I did that:

    Create member
    CurrentCube.[Limit Type].[Limit Type].[Ave mR]
    AS AGGREGATE(LinkMember([Limit From Date].[Limit From Date].CurrentMember,[Date].[Fiscal Date]):
    LinkMember([Limit To Date].[Limit To Date].CurrentMember,[Date].[Fiscal Date]),[Limit Type].[Limit Type].[mR])
    /
    COUNT(LinkMember([Limit From Date].[Limit From Date].CurrentMember,[Date].[Fiscal Date]):
    LinkMember([Limit To Date].[Limit To Date].CurrentMember,[Date].[Fiscal Date]));


    Regards
    Jamie
    Monday, July 13, 2009 2:10 PM

Answers

  • Hi jamie

    You can use the AVG() function rather than doing SUM()/COUNT() . and Yes you can use only one dimension ( I am using DATE dimension of adventure works). If you are getting this dynamically in some report you will have to dynamically create Set using STrToSET(). Have  alook at the query below

    WITH
    SET  MyDateRange AS {[Date].[Fiscal].[Month].&[2002]&[7]:[Date].[Fiscal].[Month].&[2002]&[9]}
    MEMBER [Measures].[Avg] AS AVG(([Measures].[Internet Sales Amount],MyDateRange)),FORMAT_STRING = 'Currency'
    
    SELECT {[Measures].[Internet Sales Amount],[Measures].[Avg]} on 0 
    ,{MyDateRange} on 1 
    from [Adventure Works]
    Result

      Internet Sales Amount Avg
    Jul-02 $500,365.16 $465,611.21
    Aug-02 $546,001.47 $465,611.21
    Sep-02 $350,466.99 $465,611.21


    let us know how you get along

    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, July 16, 2009 10:14 AM
    Answerer

All replies

  • Hi Jamie,

    It sounds to me like maybe you should just change the Measure AggregateFunction to Average or AverageOfChildren - am I missing something?

    Mike
    Thursday, July 16, 2009 7:46 AM
  • Hi jamie

    You can use the AVG() function rather than doing SUM()/COUNT() . and Yes you can use only one dimension ( I am using DATE dimension of adventure works). If you are getting this dynamically in some report you will have to dynamically create Set using STrToSET(). Have  alook at the query below

    WITH
    SET  MyDateRange AS {[Date].[Fiscal].[Month].&[2002]&[7]:[Date].[Fiscal].[Month].&[2002]&[9]}
    MEMBER [Measures].[Avg] AS AVG(([Measures].[Internet Sales Amount],MyDateRange)),FORMAT_STRING = 'Currency'
    
    SELECT {[Measures].[Internet Sales Amount],[Measures].[Avg]} on 0 
    ,{MyDateRange} on 1 
    from [Adventure Works]
    Result

      Internet Sales Amount Avg
    Jul-02 $500,365.16 $465,611.21
    Aug-02 $546,001.47 $465,611.21
    Sep-02 $350,466.99 $465,611.21


    let us know how you get along

    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, July 16, 2009 10:14 AM
    Answerer