MDX-TopCount加上“其他"或“其余"分组(在一组成员中) [英] MDX - TopCount plus 'Other' or 'The Rest' by group (over a set of members)
问题描述
我必须按客户组显示前5名客户的销售额，但该组内其他客户的销售额汇总为其他".与此问题类似，但针对每个客户组分别计算
I've got requirement to display top 5 customer sales by customer group, but with other customers sales within the group aggregated as 'Others'. Something similar to this question, but counted separately for each of customer groups.
根据 MSDN 在一组上执行TopCount成员，您必须使用Generate函数.
According to MSDN to perform TopCount, over a set of members you have to use Generate function.
这部分工作正常:
with
set [Top5CustomerByGroup] AS
GENERATE
(
[Klient].[Grupa Klientow].[Grupa Klientow].ALLMEMBERS,
TOPCOUNT
(
[Klient].[Grupa Klientow].CURRENTMEMBER * [Klient].[Klient].[Klient].MEMBERS
, 5
, [Measures].[Przychody ze sprzedazy rzeczywiste wartosc]
)
)
SELECT
{ [Measures].[Przychody ze sprzedazy rzeczywiste wartosc]} ON COLUMNS,
{
[Klient].[Grupa Klientow].[Grupa Klientow].ALLMEMBERS * [Klient].[Klient].[All], --for drilldown purposes
[Top5CustomerByGroup]
}
ON ROWS
FROM
(
SELECT ({[Data].[Rok].&[2013]} ) ON COLUMNS
FROM [MyCube]
)
但是我对其他"部分有疑问.
however I've got problem with 'Others' part.
我认为我可以按组与其他客户构建集合(数据看起来不错)，
I think I was able to construct set with other customers by group (data looks good) as:
set [OtherCustomersByGroup] AS
GENERATE
(
[Klient].[Grupa Klientow].[Grupa Klientow].ALLMEMBERS,
except
(
{[Klient].[Grupa Klientow].CURRENTMEMBER * [Klient].[Klient].[Klient].MEMBERS},
TOPCOUNT
(
[Klient].[Grupa Klientow].CURRENTMEMBER * [Klient].[Klient].[Klient].MEMBERS
, 5
, [Measures].[Przychody ze sprzedazy rzeczywiste wartosc]
)
)
)
但是我不知道如何通过分组对其进行汇总.
however I don't have idea how to aggregate it with grouping.
按照此问题
member [Klient].[Klient].[tmp] as
aggregate([OtherCustomersByGroup])
产生一个逻辑上的值.
produces one value, which is logical.
我认为我需要每个组中具有其他"客户的集合列表，而不是单个[OtherCustomersByGroup]集合，但是我不知道如何构造它们.
I think I need list of sets with 'other' customers in each group instead of single [OtherCustomersByGroup] set, but don't have idea how to construct them.
有人有什么想法或建议吗?
Does anyone have any ideas or suggestions?
更新:
对我的需求有些误解.我需要按销售额将每个客户组中的前n个客户与该组中其他客户的销售额汇总到一个位置(假设为其他).
There is some misunderstanding of my needs. I need Top n customers within each of customer group by sales with sales of other customers in this group aggregated to one position (let's say called Others).
例如，此简化输入的内容:
For example for this simplified input:
| Group | Client | Sales |
|--------|----------|--------|
| Group1 | Client1 | 300 |
| Group1 | Client2 | 5 |
| Group1 | Client3 | 400 |
| Group1 | Client4 | 150 |
| Group1 | Client5 | 651 |
| Group1 | Client6 | null |
| Group2 | Client7 | 11 |
| Group2 | Client8 | 52 |
| Group2 | Client9 | 44 |
| Group2 | Client10 | 21 |
| Group2 | Client11 | 201 |
| Group2 | Client12 | 325 |
| Group2 | Client13 | 251 |
| Group3 | Client14 | 15 |
我需要这样的输出(这里是前2个):
I need such output (here is top 2):
| Group | Client | Sales |
|--------|----------|--------|
| Group1 | Client5 | 651 |
| Group1 | Client3 | 400 |
| Group1 | Others | 455 |
| Group2 | Client12 | 325 |
| Group2 | Client13 | 251 |
| Group2 | Others | 329 |
| Group3 | Client14 | 15 |
| Group3 | Others | null | <- optional row
不需要排序，我们将在客户端进行处理.
Sorting isn't required, we are going to process it by client side.
推荐答案
以下内容与AdvWrks背道而驰，并使用了我在Chris Webb的博客上看到的一种技术，他在此概述了该技术:
https://cwebbbi.wordpress.com/2007/06/25/advanced-ranking-and-dynamically-generated-named-sets-in-mdx/
The following is against AdvWrks and uses a technique I saw on Chris Webb's blog which he outlines here:
https://cwebbbi.wordpress.com/2007/06/25/advanced-ranking-and-dynamically-generated-named-sets-in-mdx/
创建集合MyMonthsWithEmployeesSets的脚本部分，我很难理解-也许@AlexPeshik可以进一步说明以下脚本中发生的事情.
The section of the script that creates the set MyMonthsWithEmployeesSets I find very difficult to get my head around - maybe @AlexPeshik could shed a little more light on what is happening in the following script.
WITH
SET MyMonths AS
TopPercent
(
[Date].[Calendar].[Month].MEMBERS
,20
,[Measures].[Reseller Sales Amount]
)
SET MyEmployees AS
[Employee].[Employee].[Employee].MEMBERS
SET MyMonthsWithEmployeesSets AS
Generate
(
MyMonths
,Union
(
{[Date].[Calendar].CurrentMember}
,StrToSet
("
Intersect({},
{TopCount(MyEmployees, 10, ([Measures].[Reseller Sales Amount],[Date].[Calendar].CurrentMember))
as EmployeeSet"
+
Cstr(MyMonths.CurrentOrdinal)
+ "})"
)
)
)
MEMBER [Employee].[Employee].[RestOfEmployees] AS
Aggregate
(
Except
(
MyEmployees
,StrToSet
(
"EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths))
)
)
)
MEMBER [Measures].[EmployeeRank] AS
Rank
(
[Employee].[Employee].CurrentMember
,StrToSet
(
"EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths))
)
)
SELECT
{
[Measures].[EmployeeRank]
,[Measures].[Reseller Sales Amount]
} ON 0
,Generate
(
Hierarchize(MyMonthsWithEmployeesSets)
,
[Date].[Calendar].CurrentMember
*
{
Order
(
Filter
(
MyEmployees
,
[Measures].[EmployeeRank] > 0
)
,[Measures].[Reseller Sales Amount]
,BDESC
)
,[Employee].[Employee].[RestOfEmployees]
}
) ON 1
FROM [Adventure Works];
编辑-亚历克斯第三次尝试的解决方案:
Edit - solution for Alex's third attempt:
WITH
SET [AllCountries] AS [Country].[Country].MEMBERS
SET [AllStates] AS [State-Province].[State-Province].MEMBERS
SET [Top2States] AS
Generate
(
[AllCountries]
,TopCount
(
(EXISTING
[AllStates])
,3
,[Measures].[Internet Order Count]
)
)
MEMBER [State-Province].[All].[RestOfCountry] AS
Aggregate({(EXISTING {[AllStates]} - [Top2States])})
SELECT
{[Measures].[Internet Order Count]} ON COLUMNS
,{
[AllCountries]
*
{
[Top2States]
,[State-Province].[All].[RestOfCountry]
,[State-Province].[All]
}
} ON ROWS
FROM [Adventure Works];
这篇关于MDX-TopCount加上“其他"或“其余"分组(在一组成员中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
