Dynamic Sets and Calculations

• filip.bouckaert 97385

  Grasshopper

  Points: 21

  More actions

  October 30, 2015 at 9:53 am

  #149135

  I want to do an ABC-Analysis of the Items sold (A being top 20%, B between 20% and 80%, C the rest)

  I've created in the cube 3 Dynamic Sets [A], and [C] and a calculated measure to set the group

  CREATE DYNAMIC SET CURRENTCUBE.[Set for ABC] AS

  NonEmpty( [Item].[Item].[Item].MEMBERS,

  [Measures].[Net Value] );

  CREATE DYNAMIC SET CURRENTCUBE.[A1] AS

  TopPercent( [Set for ABC], 20, [Measures].[Net Value] );

  CREATE DYNAMIC SET CURRENTCUBE.[B1] AS

  except(

  TopPercent( [Set for ABC], 80, [Measures].[Net Value] )

  , TopPercent( [Set for ABC], 20, [Measures].[Net Value1] ));

  CREATE DYNAMIC SET CURRENTCUBE.[C1] AS

  except([Set for ABC]

  ,TopPercent( [Set for ABC], 80, [Measures].[Net Value] ));

  -- Sets Group based on membership

  CREATE MEMBER CURRENTCUBE.[Measures].[ABC Group] AS

  iif(IsEmpty( [Measures].[Measure for ABC] ), null,

  iif(Exists( [Item].[Item].[Item], [A]).count >0,'A'

  ,iif(Exists( [Item].[Item].[Item], ).count >0,'B'

  ,'C')));

  When I run a query it allways returns 'A' as the group.

  When I set a calculated measure in my query and select that value I get the proper group.

  With MEMBER [Measures].[ABC Group New] AS

  iif(IsEmpty( [Measures].[Measure for ABC] ), null,

  iif(intersect( [A],[Item].[Item].currentmember).count >0,'A'

  ,iif(intersect( ,[Item].[Item].currentmember).count >0,'B'

  ,'C')))

  306,75AC

  90,32 AC

  93,14 AC

  5,4 AC

  743,1 AC

  193,74AC

  103,52AC

  1027,38AC

  41,18 AC

  1148,03AC

  276,08AC

  405 AC

  72,06 AC

  It seems that for the cube calculated measure the set [A] contains all items i.s.o. the top 20 percent.