Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Cumulative Totals over specific Date Range Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 19, 2014 4:10 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, May 26, 2015 9:03 AM Points: 216, Visits: 789
 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.HitsIf 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 1FROM [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
Post #1583685
 Posted Thursday, June 19, 2014 8:25 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 12:20 PM Points: 1,162, Visits: 2,130
 Change the first part of your range in your calculation to Axis(1).Item(0)So in AdventureWorks the example would be:`WITHMEMBER Measures.toto ASAggregate({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 1FROM[Adventure Works]` I'm on LinkedIn
Post #1583858
 Posted Thursday, June 19, 2014 9:16 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, May 26, 2015 9:03 AM Points: 216, Visits: 789
 HiThanks 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
Post #1583891

 Permissions