我需要使用单个 MDX 查询根据两个条件获取聚合值
Am having below data
我在下面的查询中使用 IIF() 函数创建了两个计算函数
WITH
MEMBER [Measures].[Expression1] AS IIF([EMPLOYEE].[TITLE].CURRENTMEMBER.MEMBERVALUE = "SALES REPRESENTATIVE",[Reseller Order Count],0)
MEMBER [Measures].[Expression2] AS IIF([EMPLOYEE].[TITLE].CURRENTMEMBER.MEMBERVALUE = "SALES REPRESENTATIVE",0,[Reseller Order Count]) SELECT{
[Measures].[Expression1],
[Measures].[Expression2]} ON COLUMNS FROM [Sales Targets] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING
输出必须是 3693 和 103,但返回如下
查询中是否需要进行任何更改?
谁能指导我得到正确的结果吗?
您的想法是正确的,但需要跨员工职称执行此计算:
WITH
MEMBER [Measures].[Expression1] AS
SUM(
[EMPLOYEE].[TITLE].[TITLE].MEMBERS,
IIF([EMPLOYEE].[TITLE].CURRENTMEMBER IS [EMPLOYEE].[TITLE].[SALES REPRESENTATIVE],[Reseller Order Count],Null)
)
MEMBER [Measures].[Expression2] AS
SUM(
[EMPLOYEE].[TITLE].[TITLE].MEMBERS,
IIF([EMPLOYEE].[TITLE].CURRENTMEMBER IS [EMPLOYEE].[TITLE].[SALES REPRESENTATIVE],Null,[Reseller Order Count])
)
SELECT{
[Measures].[Expression1],
[Measures].[Expression2]} ON COLUMNS FROM [Sales Targets] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)