0

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

15replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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]

    )

     

     

     

    Reply Like
  • 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,

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

     

    Thanks,

    Anthony

    Reply Like
  • 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

     

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

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

    Reply Like
  • 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.

     

     

     

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

     

    Reply Like
  • thank you

    Reply Like
  • 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]

    )

    Reply Like
  • 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

    Reply Like
  • 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

     

    Reply Like
  • 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

    Reply Like
  • 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

    Reply Like
  • please contact us for an online session

    Reply Like
login to reply
Like Follow
  • Status Answered
  • 6 mths agoLast active
  • 15Replies
  • 140Views
  • 2 Following