locked
Having a problem getting SUM function to work as expected RRS feed

  • Question

  • Hi all,

    I have the following:

    CREATE MEMBER Measure.a
    AS
    SUM([Pricing Conditions].[Pricing Conditions].&[5],
    IIF([Measures].[Regular Cases]>0,1,NULL))

    FYI, Pricing Condition dimension is a varchar datatype.

    When I execute this measure against the products dimension in SSMS I get the results I want (1 if the condition is true and Null otherwise). My prob is when I create this as a Calculated Measure. The Sum total comes up as 1 and not the total of Sum of all 1's in the Cube Browser.

    What am I doing wrong???

    Wednesday, December 1, 2010 4:50 PM

Answers

  • After having tried all the suggestions and still reaching a dead end on this; in the end I resorted to adding another field to my fact table that simply held an integer value of 1 if the product record met the pricing condition I was targeting or NULL if it didn't. I then created a hidden dimension from the data that had a dimensional key of 0 if the underlying products didn't meet the condition and 1 if they did.

    Then the following code gave me the correct SUM of the products that met the pricing condition I was after:

    CREATE MEMBER Measure.ConditionCt
    AS
    SUM(Existing Decendants ([Discounts].[Discounted Price Condition].&[1],,AFTER),
    IIF([Measures].[Regular Cases]>0,1,NULL))

    I still can't figure out why the latter didn't work and will likely continue to explore that along with your suggestions to see if I can't make it work the other way but for now the new approached seems to work effortlessly.

    I divided the votes between the both of you for being extremely helpful and assisting me to look at the issue from a different perspective. I really appreciate all of your responses and wouldn't have got this far without your help.

    Thanks Guys!!!

    • Marked as answer by mindscape777 Monday, December 6, 2010 12:52 PM
    Monday, December 6, 2010 12:47 PM

All replies

  • One issue I see here is that you are referring to [Pricing Conditions].[Pricing Conditions].&[5], that will only return you a 1 or a NULL for the measure.

    -Remember to mark as helpful/the answer if you agree with the post.
    Wednesday, December 1, 2010 5:28 PM
  • Ok, I get that. So how would one go about summing those values returned by the Measure???

    Wednesday, December 1, 2010 5:38 PM
  • You pass a set as the first parameter and measure as the second to SUM the measure over the set

    In your case, if [Pricing Conditions].[Pricing Conditions].&[5] has children you want to sum across, you can use
    SUM([Pricing Conditions].[Pricing Conditions].&[5].CHILDREN, IIF([Measures].[Regular Cases]>0,1,NULL))

    HTH


    -Remember to mark as helpful/the answer if you agree with the post.
    Wednesday, December 1, 2010 5:41 PM
  • Then I believe the problem in this case is that [Pricing Conditions].[Pricing Conditions].&[5] is a key with no children and so that proposition won't work.

    What my existing code is doing is displaying a value of 1 if a product meets the pricing condition key. So given the following Code:

    CREATE MEMBER Measure.ConditionCt
    AS
    SUM([Pricing Conditions].[Pricing Conditions].&[5],
    IIF([Measures].[Regular Cases]>0,1,NULL))

    SELECT Measures.ConditionCt
    ON 0
    ,({[Products].[Product ID].&[40139],
    [Products].[Product ID].&[51029],
    [Products].[Product ID].&[51235],
    [Products].[Product ID].&[9000111]})
    ON 1
    FROM [Sales]

    Gives me a single count for whether or not a specified product meets the pricing condition based on key 5.

    ProductName    ConditionCt
    40139                     1
    51029                     1
    51235                  (null)
    9000111                 1

    The PricingConditions dimension key is a numerical value that ranges from 1 to 5 so I only want to query against the 5th key and take a count. If I were to go to the parent level, I could use your code but the results wouldn't be what I'm after.

    I need a way to Sum the results of my existing code so that the Sum would show in this case 3 instead of 1. Is this not possible or am I going about it the wrong way???

    Wednesday, December 1, 2010 6:13 PM
  • You can use the MDX Exists function to filter out the products that meet the Pricing Condition 5.  Then use the resulting set with the conditional IIF statement to sum.

    Sum( Exists ([Products].[Product ID].[Product ID], [Pricing Conditions].[Pricing Conditions].&[5]),

       IIF(Measures.[Regular Cases] > 0, 1, NULL))




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Wednesday, December 1, 2010 6:28 PM
  • Try this:

    CREATE MEMBER Measure.ConditionCt
    AS
    SUM(([Pricing Conditions].[Pricing Conditions].&[5],[Products].[Product ID].[Product ID])
    IIF([Measures].[Regular Cases]>0,1,NULL))


    -Remember to mark as helpful/the answer if you agree with the post.
    Wednesday, December 1, 2010 6:34 PM
  • Thanks for trying guys. I tried both of the suggested codes but neither one of them returned the actual SUM that I needed. The behavior remains as the value 1 for the total SUM of all records returned I am at a total loss at this point how to make this work.

    I gladly welcome any other suggestions. I don't believe the solution should be this difficult but maybe there is something I'm still missing.

    Thursday, December 2, 2010 3:51 AM
  • You have put the Product Name on the rows axis and are trying to get a SUM for the product name, and that is going to be 1 if the measure value is > 0.

    Try putting another dimension (date, maybe) on the rows axis and see if you get the SUM to work


    -Remember to mark as helpful/the answer if you agree with the post.
    Thursday, December 2, 2010 3:58 AM
  • In addition to Naveen's suggestion, I would add that it may be a good idea to break it in to parts - 1) get the correct set that includes only members where pricing condition is 5 and 2) once you have the correct set then display all the product members in the 1st axis (rows) with the conditional measure on the columns.    A third and last step, once the above is working, would be to remove the products from the rows axis so the one row returned is the total aggregate of the measure




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, December 2, 2010 1:32 PM
  • After having tried all the suggestions and still reaching a dead end on this; in the end I resorted to adding another field to my fact table that simply held an integer value of 1 if the product record met the pricing condition I was targeting or NULL if it didn't. I then created a hidden dimension from the data that had a dimensional key of 0 if the underlying products didn't meet the condition and 1 if they did.

    Then the following code gave me the correct SUM of the products that met the pricing condition I was after:

    CREATE MEMBER Measure.ConditionCt
    AS
    SUM(Existing Decendants ([Discounts].[Discounted Price Condition].&[1],,AFTER),
    IIF([Measures].[Regular Cases]>0,1,NULL))

    I still can't figure out why the latter didn't work and will likely continue to explore that along with your suggestions to see if I can't make it work the other way but for now the new approached seems to work effortlessly.

    I divided the votes between the both of you for being extremely helpful and assisting me to look at the issue from a different perspective. I really appreciate all of your responses and wouldn't have got this far without your help.

    Thanks Guys!!!

    • Marked as answer by mindscape777 Monday, December 6, 2010 12:52 PM
    Monday, December 6, 2010 12:47 PM