Cumulative Totals over specific Date Range

  • Hi all,

    I would like to implement a running total of a measure over Days, Weeks, Months, ... in an MDX query and ultimately as a Calculation in my Cube.

    I know that there are a lot of articles out there and I have found a few solutions but none of which works within the context of a specific Date Rage.

    Setting:

    Time Dimension [DimTime].[Date].[Date] and [DimTime].[Week].[Week]

    Measure to aggregate: Measure.Hits

    If I run the following query:

    WITH

    MEMBER Measures.toto AS

    Aggregate({[DimTime].[Date].[Date].Members.Item(0):[DimTime].[Date].currentmember}, [Measures].[Hits]))

    SELECT

    {Measures.[Hits], Measures.toto} ON 0,

    NON EMPTY[DimTime].[Date].[Date] ON 1

    FROM

    [My Cube]

    It works fine however if I had a specific Date Range in the "WHERE" Clause or directly in the "SELECT", it aggregates values outside the Date Range.

    To simplify, I would like the aggregation to start at the first Date in the Query and not before.

    I have played with Aggregate, Sum, ... and no joy. 🙁

    I'm sure some of you will point out what I have stupidly missed.

    Cheers

  • Change the first part of your range in your calculation to Axis(1).Item(0)

    So in AdventureWorks the example would be:

    WITH

    MEMBER Measures.toto AS

    Aggregate({Axis(1).Item(0):[Date].[Date].currentmember},

    [Measures].[Order Count])

    SELECT

    {[Measures].[Order Count], Measures.toto} ON 0,

    NON EMPTY {[Date].[Date].&[20060301]:[Date].[Date].&[20060331]} ON 1

    FROM

    [Adventure Works]


    I'm on LinkedIn

  • Hi

    Thanks for this solution which works fine.

    Having said that, it only works if the Data Range is specified in the SELECT part of the query.

    If I put the Range in the WHERE Clause, it still aggregates/sum from all the previous dates.

    Thanks for spending the time helping me!

    I'll carry on digging and will put a solution ... if/when I find one.

    Cheers

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply