Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cumulative Totals over specific Date Range Expand / Collapse
Author
Message
Posted Thursday, June 19, 2014 4:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 4:12 AM
Points: 216, Visits: 774
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
Post #1583685
Posted Thursday, June 19, 2014 8:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 476, Visits: 917
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
Post #1583858
Posted Thursday, June 19, 2014 9:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 4:12 AM
Points: 216, Visits: 774
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
Post #1583891
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse