June 19, 2014 at 4:10 am
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
June 19, 2014 at 8:25 am
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]
June 19, 2014 at 9:16 am
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