June 23, 2010 at 10:03 am
hi All,
Can you help with below...am trying to get running sales total on some products...it comes out wrong....is the definition of my [running total] member correct ?
-- with set picking out a set of specified members and running totals not working
WITH
SET [Selected Products]
AS
{
[Dim Product].[English Product Name].&[Mountain-200 Black, 38],
[Dim Product].[English Product Name].&[Mountain-200 Black, 46],
[Dim Product].[English Product Name].&[Mountain-200 Silver, 38]
}
MEMBER [Running Totals] AS 'SUM (null : [Dim Product].[English Product Name].CurrentMember, [Measures].[Sales Amount])'
SELECT {[Measures].[Sales Amount],[Running Totals]} ON 0,
[Selected Products] ON 1
FROM dsvAdventureWorksDW
Results
=======
Product Sales AmountRunning Totals Should be
Mountain-200 Black, 381294866.14123200703.1412 1294866.1412
Mountain-200 Black, 461373469.54825937314.78279999 2668335.6894
Mountain-200 Silver, 381339462.79047276777.57319999 4007798.4799
July 2, 2010 at 8:44 am
hi All,
Finally cracked it...after trawling the net...not saying it is not the only solution, but one that works...
I used the rank function to bring back ordinal position of member in the set
and then the head function that sums to the ordinal position....
WITH
SET [Selected Products]
AS
{
[Dim Product].[English Product Name].&[Mountain-200 Black, 38],
[Dim Product].[English Product Name].&[Mountain-200 Black, 46],
[Dim Product].[English Product Name].&[Mountain-200 Silver, 38]
}
MEMBER [OffSet] AS rank([Dim Product].[English Product Name],[Selected Products]) -- <=== brings back ordinal position of member in set
MEMBER [Running Totals] AS 'SUM(HEAD([Selected Products], [OffSet]),[Measures].[Sales Amount])'
SELECT {[Measures].[Sales Amount], [Running Totals]} ON 0,
[Selected Products] ON 1
FROM dsvAdventureWorksDW
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply