Post reply

Creating an MDX named set in SSAS using, tail &, toppercent

  • October 9, 2012 at 8:07 am

    #263665

    I need to pull back the top 20 % customers that has spent the most money in the past 36 months. How i do go about doing this?

    I cannot get this mdx statement to work, help!

    select [Measures].[Net Sales Value] on columns,

    {tail([Order Date].[Order Date].[Month],36),toppercent({[Customer Information].[Customer Acc].[Person No].members},20,[Measures].[Net Sales Value])} on rows

    from marketingcube

    Executing the query ...

    Query (2, 1) Two sets specified in the function have different dimensionality.

  • October 12, 2012 at 8:10 am

    #1548661

    I think your date needs to be associated with the measure like below

    select [Measures].[Net Sales Value] on columns,

    toppercent({[Customer Information].[Customer Acc].[Person No].members},20,Sum(tail([Order Date].[Order Date].[Month],36) ,[Measures].[Net Sales Value])) on rows

    from marketingcube

    Lemme know if it works

    Mack

  • October 12, 2012 at 8:19 am

    #1548672

    I guess the issue with the MDX I posted is that you are querying the revenue without specifying a date

    The following MDX may work (although I have syntax checked it)

    select [Measures].[Net Sales Value] on columns,

    toppercent({[Customer Information].[Customer Acc].[Person No].members},20,[Measures].[Net Sales Value]) on rows

    from

    (select tail([Order Date].[Order Date].[Month],36) on columns from marketingcube)

    Doing a sub-cube will limit the data to the last 36 months

    Mack

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply