I'm newbie in MDX,
could anyone please help on the following questions
I've following MDX
WITH
SET [topZ%]
AS 'TOPPERCENT(NONEMPTYCROSSJOIN({[product].[productcode].CHILDREN},{[Timeofday].[Dt].&[2006-09-01T00:00:00]}),70,([Measures].[sales]))'
//% contribution to the total
MEMBER [Measures].[%ofTot] AS '[Measures].[sales]/([Measures].[sales],[product].[productcode].[All])',FORMAT_STRING="PERCENT"
//Cumulative %
MEMBER [Measures].[Cum%] AS 'SUM({[topZ%].FIRSTSIBLING:[topZ%].CURRENTMEMBER},[Measures].[%ofTot])'
SELECT
{[Measures].[sales],[Measures].[%ofTot],[Measures].[Cum%] } on 0,
{[topZ%]} on 1
from testcube
I want the result as follows:
sales %ofTot Cum%
productA 2006-09-01 300 50% 50%
productB 2006-09-01 200 33% 83%
productC 2006-09-01 100 17% 100%
But there's problem on the [Cum%] column, its show #Error
Could someone please give some advice ? thanks a lot
Not sure how general a solution you need, but you can try something like:
MEMBER [Measures].[Cum%] AS
'SUM(Head([topZ%],
Rank(([product].[productcode].CurrentMember, [Timeofday].[Dt].CurrentMember),
[topZ%])), [Measures].[sales])
/([Measures].[sales],[product].[productcode].[All])',
FORMAT_STRING="PERCENT"
|||I see.thanks deepak
