MDX之Case When用法

with 
member [Measures].[终端销售数量总计] as 
sum(ytd([日期].[年月].CurrentMember),[Measures].[终端销售数量])

member [Measures].[仓库出货数量] as 
sum(ytd([日期].[年月].CurrentMember)*[物流类型].[物流类型ID].&[仓库出货],[Measures].[渠道出货数量_出货日期])

member [Measures].[收退数量] as
sum(ytd([日期].[年月].CurrentMember)*[物流类型].[物流类型ID].&[仓库收退],[Measures].[渠道收货数量])
+
sum(ytd([日期].[年月].CurrentMember)*[物流类型].[物流类型ID].&[店仓调拨],[Measures].[渠道收货数量])


member [Measures].[销售数量] as
[Measures].[终端销售数量总计]+[Measures].[仓库出货数量]-[Measures].[收退数量]

member [Measures].[月均销售数量1] as
 [Measures].[销售数量]/left( [日期].[年月].CurrentMember.Properties('Name'),2) --left截取
  ,Format_String = "#,##0.00"
  
  member [Measures].[月均销售数量] as
  case 
  when ([日期].[年月].currentmember.parent.children.item(0),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/left( [日期].[年月].CurrentMember.Properties('Name'),2)
  when ([日期].[年月].currentmember.parent.children.item(1),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-1)
  when ([日期].[年月].currentmember.parent.children.item(2),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-2)
  when ([日期].[年月].currentmember.parent.children.item(3),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-3)
  when ([日期].[年月].currentmember.parent.children.item(4),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-4)
  when ([日期].[年月].currentmember.parent.children.item(5),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-5)
  when ([日期].[年月].currentmember.parent.children.item(6),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-6)
  when ([日期].[年月].currentmember.parent.children.item(7),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-7)
  when ([日期].[年月].currentmember.parent.children.item(8),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-8)
  when ([日期].[年月].currentmember.parent.children.item(9),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-9)
  when ([日期].[年月].currentmember.parent.children.item(10),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-10)
  when ([日期].[年月].currentmember.parent.children.item(11),[Measures].[终端销售数量])<>null 
    then [Measures].[销售数量]/(left( [日期].[年月].CurrentMember.Properties('Name'),2)-11)
  else null end
 ,Format_String = "#,##0.00"

select {
[Measures].[终端销售数量总计],[Measures].[仓库出货数量],[Measures].[收退数量],
[Measures].[销售数量],[Measures].[月均销售数量1],[Measures].[月均销售数量] 
} on 0,
non empty{
[货品].[品牌].[品牌]
} on 1
from [YeehooCube]
where [日期].[年月].[年].&[2013].&[05月]

 

转载于:https://www.cnblogs.com/ycdx2001/p/3290785.html