Google Track

Friday, June 25, 2010

MDX Calculations

WITH
MEMBER [Measures].[SumOfAG] AS
'( Sum({[Time]}, [Measures].[RunningTotalSubs]))'

MEMBER [Measures].[SuperSum] AS
' (Sum({[Age Group].[13 and 17],[Age Group].[18 and 24],[Age Group].[25 and 34],[Age Group].[35 and 44],[Age Group].[45 and 54],[Age Group].[over 55],[Age Group].[less then 13],[Age Group].[UNKNOWN] },[Measures].[SumOfAG] ))'

MEMBER [Measures].[GMale] AS
'( Sum({([Time],[Gender].[Male])}, [Measures].[RunningTotalSubs]))'
MEMBER [Measures].[GMalePer] AS
' ([Measures].[GMale] / [Measures].[SuperSum])'
MEMBER [Measures].[GFemale] AS
'( Sum({([Time],[Gender].[Female])}, [Measures].[RunningTotalSubs]))'

MEMBER [Measures].[GFemalePer] AS
' ([Measures].[GFemale] / [Measures].[SuperSum])'

MEMBER [Measures].[GUnknown] AS
'( Sum({([Time],[Gender].[Unknown])}, [Measures].[RunningTotalSubs]))'

MEMBER [Measures].[GUnknownPer] AS
' ([Measures].[GUnknown] / [Measures].[SuperSum])'

select {[Measures].[SumOfAG], [Measures].[GMale], [Measures].[GMalePer], [Measures].[GFemale], [Measures].[GFemalePer], [Measures].[GUnknown], [Measures].[GUnknownPer] }
on Columns, { ([Age Group].children) } on Rows from [consumers] where ([Zone Id].&[501])

WITH
MEMBER [Measures].[p1] AS '( [Time].[2008].&[7].[31].[30], [Subs Count] ) '
MEMBER [Measures].[p2] AS '( [Time].[2008].&[6].[27].[30], [Net Subs] ) '
MEMBER [Measures].[Variant Percentage] AS '(( [Time].[2008].&[7].[31].[30], [Net Subs] ) -( [Time].[2008].&[6].[27].[30], [Net Subs] ))/( [Time].[2008].&[6].[27].[30], [Net Subs] ) '
MEMBER [Measures].[SubsDiff] AS '(( [Time].[2008].&[7].[31].[30], [Net Subs] ) -( [Time].[2008].&[6].[27].[30], [Net Subs] )) '

select {[Subs Count], [Net Subs],[RunningTotalSubs],[Measures].[p1], [Measures].[p2], [Measures].[SubsDiff], [Measures].[Variant Percentage] } on columns,

TopCount(NonEmptyCrossJoin({Descendants([Source].Currentmember, 3)},{Descendants([Zone Id].&[1178],2)}) ,150, ([Net Subs])) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_KEY on rows

FROM [consumers]

1 comment:

Unknown said...

What is the purpose of the second query?