locked
MDX - Rolling 2 Day Sum RRS feed

  • Question

  • Hello,
    I'm wondering what's the best way to sum up a measure for the last two days?
    For example.
    11/1  4 conversion
    11/2  3 conversion
    11/3  1 conversion
    11/4  2 conversion

    If 11/4 is selected, measure will show 4.  If 11/3 is selected, measure will show 7.  If 11/5 is selected, measure will show 3.
    Here's what I have so far.

    CREATE SET CURRENTCUBE.[Days With Data]

     AS NONEMPTY([Time].[Time Hierarchy].[Day].MEMBERS);

    CREATE SET CURRENTCUBE.[Rolling 2 Days]

     AS TAIL([Days With Data],2).ITEM(0) : TAIL([Days With Data],1).ITEM(0);


    Not sure how to sum and the final mdx will look like.
    Friday, November 6, 2009 12:47 AM

Answers

  • Interesting.  Want to confirm we are talking about the same thing. 

    Objective - [Rolling 2 Day Conversions] should work with either of the two dimensions, meaning I can pick day members from either of the dimensions below.
    [Date].[Day]
    [Fiscal].[Day]
    User will not pick members from both dimensions at the same time.


    No, we are talking about different things. I thought you wanted the calc to work across mulitple measures. To make it work across 2 time dimensions we would have to define it differently as we have to explicitly reference a .CurrentMember.

    So what I would do would be to pick one (eg Date) as a default for the calculation...

       CREATE MEMBER Measures.[Rolling 2 Day Conversions]
       AS SUM(TAIL(NONEMPTY(null:[Date].[Day].CurrentMember,[Measures].[Conversions]),2),[Measures].[Conversions]);

    And then I would add a SCOPE statement to override the calc when we are using the other (Fiscal) dimension. The way to detect this is to check if the first dimension (Date) is at the ALL level, then by implication, the user is using the other dimension (Fiscal).

       SCOPE (Measures.[Rolling 2 Day Conversions], [Date].[Day].[All]);
          this = SUM(TAIL(NONEMPTY(null:[Fiscal].[Day].CurrentMember, [Measures].[Conversions]),2),[Measures].[Conversions]);
       END SCOPE;


    Although, commonly Fiscal and Calendar are just different hierarchies of the one dimension and share a common Day level, so this is not so much of an issue, it is more with different dates (such as OrderDate and ShipDate) that this is an issue.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Raymond-Lee Monday, November 16, 2009 4:11 AM
    Saturday, November 7, 2009 12:46 AM

All replies

  • If you requirement is to get a running sum of the last 2 non-empty days for a specific measure it would look something like the following.

    CREATE MEMBER Measures.[Rolling 2 Day Conversions]
    AS SUM(TAIL(NONEMPTY(null:[Date].[Date].CurrentMember),2),[Measures].[Conversions]);

    Although, because I am using .CurrentMember it will really only work when you are querying at the day level.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Proposed as answer by Adam Tappis Friday, November 6, 2009 7:13 PM
    Friday, November 6, 2009 4:21 AM
  • That helped Darren.
    I am curious if this measure also needs to be associated with another standalone dimension [Fiscal].[Day], how can I incorporate that into the script you provided above.

    [Date].[Day]
    [Fiscal].[Day]

    Appreciated!
    Friday, November 6, 2009 7:05 PM
  • That helped Darren.
    I am curious if this measure also needs to be associated with another standalone dimension [Fiscal].[Day], how can I incorporate that into the script you provided above.

    [Date].[Day]
    [Fiscal].[Day]

    Appreciated!

    If you are using [Fiscal].[Day] to pick your "current" date, then you would not put the calculation in the same attribute, you would want to put it in a separate attribute (or dimension). If you use the "Add Business Intelligence" wizard in BIDS it creates a new attribute in your time dimension for these types of calculations.

    The calculation would look something like the following

    CREATE MEMBER [Fiscal].[Date Calculations].[Rolling 2 Day Conversions]
    AS AGGREGATE(TAIL(NONEMPTY(null:[Fiscal].[Date].CurrentMember),2));

    Note that I changed the SUM to AGGREGATE and removed the explicit measure reference. The Aggregate function will use the Aggregate Function of the current measure to figure out how to aggregate the current measure.

    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Friday, November 6, 2009 10:13 PM
  • Interesting.  Want to confirm we are talking about the same thing. 

    Objective - [Rolling 2 Day Conversions] should work with either of the two dimensions, meaning I can pick day members from either of the dimensions below.
    [Date].[Day]
    [Fiscal].[Day]
    User will not pick members from both dimensions at the same time.

    CREATE MEMBER Measures.[Rolling 2 Day Conversions]
    AS SUM(TAIL(NONEMPTY(null:[Date].[Day].CurrentMember),2),[Measures].[Conversions]);

    Above would work with [Date].[Day] members

    CREATE MEMBER [Fiscal].[Day].[Rolling 2 Day Conversions]
    AS AGGREGATE(TAIL(NONEMPTY(null:[Fiscal].[Day].CurrentMember),2));

    Would above work with what I described in my objective?

    Thanks!

    Saturday, November 7, 2009 12:33 AM
  • Interesting.  Want to confirm we are talking about the same thing. 

    Objective - [Rolling 2 Day Conversions] should work with either of the two dimensions, meaning I can pick day members from either of the dimensions below.
    [Date].[Day]
    [Fiscal].[Day]
    User will not pick members from both dimensions at the same time.


    No, we are talking about different things. I thought you wanted the calc to work across mulitple measures. To make it work across 2 time dimensions we would have to define it differently as we have to explicitly reference a .CurrentMember.

    So what I would do would be to pick one (eg Date) as a default for the calculation...

       CREATE MEMBER Measures.[Rolling 2 Day Conversions]
       AS SUM(TAIL(NONEMPTY(null:[Date].[Day].CurrentMember,[Measures].[Conversions]),2),[Measures].[Conversions]);

    And then I would add a SCOPE statement to override the calc when we are using the other (Fiscal) dimension. The way to detect this is to check if the first dimension (Date) is at the ALL level, then by implication, the user is using the other dimension (Fiscal).

       SCOPE (Measures.[Rolling 2 Day Conversions], [Date].[Day].[All]);
          this = SUM(TAIL(NONEMPTY(null:[Fiscal].[Day].CurrentMember, [Measures].[Conversions]),2),[Measures].[Conversions]);
       END SCOPE;


    Although, commonly Fiscal and Calendar are just different hierarchies of the one dimension and share a common Day level, so this is not so much of an issue, it is more with different dates (such as OrderDate and ShipDate) that this is an issue.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Raymond-Lee Monday, November 16, 2009 4:11 AM
    Saturday, November 7, 2009 12:46 AM
  • Thanks a bunch.
    It works great within [Date].[Day].  I'm curious is there a way to make this MDX work within a hierarchy?  
    CurrentMember does not work with hierarchy right?  So I guess I cannot really use [Date].[Date Hierarchy].[All] to simply aggregate across all hierarchy levels (such as week, month, year)?

    [Date].[Day] is within [Date].[Date Hierarchy]
    Monday, November 9, 2009 6:27 PM
  • Thanks a bunch.
    It works great within [Date].[Day].  I'm curious is there a way to make this MDX work within a hierarchy?  
    CurrentMember does not work with hierarchy right?  So I guess I cannot really use [Date].[Date Hierarchy].[All] to simply aggregate across all hierarchy levels (such as week, month, year)?

    [Date].[Day] is within [Date].[Date Hierarchy]

    CurrentMember does work with hierarchies. [Date].[Date] is a hierarchy, it's just a single level hierarchy generated when the AttributeHierarchyEnabled property is set to true. The only consideration when working with something like [Date].[Date Hierarchy] is
    what you want to see at the higher (Year, Month) levels. Assuming that you would just want to see the last 2 non-empty days you could alter the previous calculations by adding the EXISTING function to reduce the set of days down to those that exist within the current context. (you could look up the EXISTING funciton in BOL to find out more about it)

    CREATE MEMBER Measures.[Rolling 2 Day Conversions]
       AS SUM(TAIL(NONEMPTY(EXISTING null:[Date].[Day].CurrentMember,[Measures].[Conversions]),2),[Measures].[Conversions]);

    SCOPE (Measures.[Rolling 2 Day Conversions], [Date].[Day].[All]);
       this = SUM(TAIL(NONEMPTY(EXISTING null:[Fiscal].[Day].CurrentMember, [Measures].[Conversions]),2),[Measures].[Conversions]);
    END SCOPE;




    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Tuesday, November 10, 2009 7:04 AM