Combining products

Hi,

I was wondering if it is possible to group products together? In the attached picture you can see multiple products listed that have an "E" at the end while some do not. Is it possible to create a custom set to remove the "E" and combine the two produts. Example: (001-151102   :   001-151102E)? This is not possible in the data warehouse so would have to be done in Pyramid. A customer member would not work as there are to many instances of this occuring.

Thanks,

Anthony

Like Follow Reply
15replies
  • Hi,

    You can create a set ( list ) and aggregate ( combine ) the items in the list based on the item label ( name).

    Example, all customers that have their name end with "e".

     

    However if you need to have a dynamic calculation where every 2 items one ending with "e"and one not ending with "e"  are to be summed together, than I would suggest to create a custom MDX calculation where you read the name of the member and add an "e" to it and aggregate both.

     

    Example:

    MDX code for Item and Item with E

    SUM(

    {

    --current item for each row.

    [Customer].[Custom Geography Zip].CURRENTMEMBER,

    --a matching item for each row with "e" appended to the name.

    STRTOMEMBER( "[Customer].[Custom Geography Zip].[" + [Customer].[Custom Geography Zip].CURRENTMEMBER.NAME + "E]") } ,

    [Measures].[Sales]

    )

     

     

     

    Like Reply
  • Hi Micheal,

    Thank you for the response, is it possibly to filter the "e" items and put them under the non "e". So the labels would be product_code and then amount aggregated?

    The reason why i am asking is because i would like to look at it by product specifically.

    Thanks,

    Like Reply
  • Hi Micheal, i am getting this error when i am putting in the formula. Do you know how to fix this?

     

    Thanks,

    Anthony

    Like Reply
  • Hi,

    Filtering out all the "e" items is easy.

    Example:

    1. List of all the items.

    2. Remove all the "e" items.

     

    The option to crate new items in Pyramid is via creating new calculated / custom  items. So if you would like to create multiple new items it will be labor intensive to create and maintain, I would consider creating the batch naming in the DB.  On the other hand if you consider keeping the original names via a calculation across all items you could override the original value to be recalculated with the required logic.

     

    Tnx,

    m

     

    Like Reply
  • Try replacing "members" with "currentmember", as the formula expects a member rather than a set.

    Like Reply
  • Hi Micheal, This is the error i am reciving now. Thanks

    Like Reply
  • Try to keep only one [Material ID] in the code and the currentmember function expect a hierarchy rather than a level expression.

    And also in the inner currentmember  it should be currentmember .name

    Could you please post the MDX code so I can edit it.

     

     

     

    Like Reply
  •  sum(
    {
    [Product].[Material ID].[Material ID].currentmember,
    strtomember("[Product].[Material ID].[Material ID].["+ [Product].[Material ID].[Material ID].members + "E]")},
    [Measures].[Billings - Net Amt]
    )

     

    Like Reply
  • thank you

    Like Reply
  • try 1st to see the string returned into the calculation

    "[Product].[Material ID].["+ [Product].[Material ID].currentmember.name + " E]"

    Once you see that the string is in the correct format apply the full formula, with adjustments if needed.

    sum(

    {

    [Product].[Material ID].currentmember,

    strtomember("[Product].[Material ID].["+ [Product].[Material ID].currentmember.name + " E]")

    },

    [Measures].[Billings - Net Amt]

    )

    Like Reply
  • Hi Micheal, This does work now, sorry for all the quesitons. The only issue i get now is products that already have the "E" at the end are reciving the "E" as well. Is there a way to eliminate these from getting an additional "E"? Thanks

    Like Reply
  • one option is to create a set that will filter out all the "E" items.

    Another option is to do the conditional  calculation

    Example:

    case

    when

    RIGHT([Product].[Material ID].currentmember.name,1) = "E"

    then

    sum([Measures].[Billings - Net Amt])

    else

    sum(

    {

    [Product].[Material ID].currentmember,

    strtomember("[Product].[Material ID].["+ [Product].[Material ID].currentmember.name + " E]")

    },

    [Measures].[Billings - Net Amt]

    )

    end

     

    Like Reply
  • case
    when
    RIGHT([Product].[Material ID].currentmember.name,1) = "E"
    then
    [Product].[Material ID].currentmember.name
    else
    ([Product].[Material ID].currentmember.name +"E]")
    end
     

    Thank you this would work for me, the caculation you have but first i need to create a set to roll everything up with an "E'" i am writing this code to create a set to add "E" to all products that do not have an "E" buy i am getting the Tuple Set error once again. Thanks

    Like Reply
  • case
     when
     RIGHT([Product].[Material ID].CURRENTMEMBER.NAME,1) = "E"
     then
     [Product].[Material ID].CURRENTMEMBER.NAME
    else
    {
    [Product].[Material ID].CURRENTMEMBER,
    STRTOMEMBER("[Product].[Material ID].["+ [Product].[Material ID].CURRENTMEMBER.NAME + "E]")
    }
    end  

     

    this customer set runs for me, but when i put the elements on it, it just gives me all and does not break out the times? Is there a specific reason? Thanks

    Like Reply
  • please contact us for an online session

    Like Reply
Mention someone by typing their name
No matching users
Like Follow
  • Status Answered
  • 6 days agoLast active
  • 15Replies
  • 38Views
  • 2 Following