MDX Top 10 high order value customers that did not order in last 6 months

Question

Hi All, Need help with finding top 10 high value customers that are idle for the last 6 months i.e no orders created. I am trying to use topcount and parallelperiod, but with no luck. Can anyone show how this can be achieved using sets on Adventure Works?? These customers should dynamically change depending on date used for slicing.

Answer 1

Hi sap9955,

Please refer to the following MDX statement:
With Set [CurrentMonth] as
{[Date].[Calendar].[Month].&[2002]&[12]}

SELECT [Measures].[Reseller Sales Amount] ON 0,
TopCount
   ({[Geography].[Geography].[City].Members 
      * LastPeriods(6, [CurrentMonth].item(0))}
   , 10
   , [Measures].[Reseller Sales Amount]
   ) ON 1
FROM [Adventure Works]

For more information about TopCount, please see:
http://technet.microsoft.com/en-us/library/ms144792.aspx

Regards,

---

Bin Long

TechNet Community Support