MDX calculation

MDX calculation

• Jimc25

  One small suggestion - you might want to express it as:

  CREATE SET CURRENTCUBE.[CTP Only Format]

  AS filter([Billing Customer Job].[CustomerName].[CustomerName], ([Measures].[CTP format billing] > 0 and [Measures].[non-ctp format billing] = 0)) ;

  In your specific case, [Billed Sales Amount] < 1 might be equivalent to 0, but not in general...

  
  
  

• papuna

  Not sure how the calculated members are returning sets of customers, but you can use Except() to remove members of one set from another, like: Except(Set1, Set2)
  
  

• RLewkovich

  The Except has to be peformed on sets of customers (presumably there is a hierarchy like [Customer].[Customer]), rather than on the calculated measures, which don't directly represent customer sets. Perhaps you could use NonEmpty() to identify the  set of customers with any non-ctp format billing, then remove that set from the rest of the customers, like:

  >>

  CREATE MEMBER CURRENTCUBE.[MEASURES].[CTP only format] AS

  aggregate(EXCEPT([Customer].[Customer].[Customer],

  NonEmpty([Customer].[Customer].[Customer],

  EXCEPT([Billing Customer Job].[FormatName].[FormatName],

  [Billing Customer Job].[FormatName].&[19]))),

  [Measures].[Billed Sales Amount]),

  FORMAT_STRING = "Currency",

  NON_EMPTY_BEHAVIOR = { [Billed Sales Amount] },

  VISIBLE = 1

  >>

  
  
  

• Xof7

  I tried your method and was unable to get it to work. However I did find a way to get what i needed so i'm posting this to help any others out with similiar issue.

  I created two calculated measures

  The first gets the first format.

  CREATE MEMBER CURRENTCUBE.[MEASURES].[CTP format billing]

  AS aggregate([Billing Customer Job].[FormatName].&[19], [Measures].[Billed Sales Amount] ),

  FORMAT_STRING = "Currency", NON_EMPTY_BEHAVIOR = { [Billed Sales Amount] }, VISIBLE = 1;

  The second measure gets all formats except the one in the previous measure

  CREATE MEMBER CURRENTCUBE.[MEASURES].[non-ctp format billing]

  AS aggregate(EXCEPT([Billing Customer Job].[FormatName].[FormatName] ,[Billing Customer Job].[FormatName].&[19]), [Measures].[Billed Sales Amount]),

  FORMAT_STRING = "Currency", NON_EMPTY_BEHAVIOR = { [Billed Sales Amount] }, VISIBLE = 1;

  Then i created a named set that uses the filter function where the second part of that function uses the two measures above

  CREATE SET CURRENTCUBE.[CTP Only Format]

  AS filter([Billing Customer Job].[CustomerName].[CustomerName], ([Measures].[CTP format billing] > 0 and [Measures].[non-ctp format billing] < 1)) ;

  Despite all the calculations this ran remarkably fast.

  thanks for your help.

  
  
  

• Ray Dodson

  I'm using AS2005

  Background: A customer could possibly have billing under both of these calculated members. What I'm trying to do is display those customers that have the "CTP format billing" only (no billing under the "non-ctp format billing").Maybe i'm going about this the wrong way. But here is what I did.

  "CTP format billing" calculated member. This returns the billing $$ tied to this format.

  CREATE MEMBER CURRENTCUBE.[MEASURES].[CTP format billing]AS sum([Billing Customer Job].[FormatName].&[19], [Measures].[Billed Sales Amount] ), FORMAT_STRING = "Currency", NON_EMPTY_BEHAVIOR = { [Billed Sales Amount] }, VISIBLE = 1;

  *************************

  "non-ctp format billing" Calculated memeber.  This returns the billing $$ for all the other formats.

  CREATE MEMBER CURRENTCUBE.[MEASURES].[non-ctp format billing]AS aggregate(EXCEPT([Billing Customer Job].[FormatName].[FormatName] ,[Billing Customer Job].[FormatName].&[19]), [Measures].[Billed Sales Amount]), FORMAT_STRING = "Currency", NON_EMPTY_BEHAVIOR = { [Billed Sales Amount] }, VISIBLE = 1;

  **************************

  "CTP only format" Calculated Member. This is where i'm using the except function to hopefully return only the CTP format billing that does not have any non-ctp format billing. However it returns the total dollar amount as if I selected the  [Measures].[Billed Sales Amount]  by itself.

  CREATE MEMBER CURRENTCUBE.[MEASURES].[CTP only format]AS aggregate(EXCEPT([CTP format billing],[non-ctp format billing]), [Measures].[Billed Sales Amount]), FORMAT_STRING = "Currency", NON_EMPTY_BEHAVIOR = { [Billed Sales Amount] }, VISIBLE = 1

  I hope I explained it clearly enough. One other note I made sure this last calculated member is after the other two in the script organizer panel.

  thanks for the help

   

   

   

   

  
  
  

• Kiwi Samurai Coder

  Please post the member definitions (hopefully, they're not too lengthy), with some description of the cube and dimensions. And are you using AS 2000 or AS 2005
  
  

• dotScience

  i used the except function the create the calculated member 2, but when i tried

  use the except function to return the calculated member 1 except for the calculated member 2 the results did not come out right.

  I ended up getting all the rows

  if it helps i could paste in the code from the calculations.

  
  
  
• MDX calculation