Comparing the sales line item of a similar kind of product

saibandaru
saibandaru
in Modeling

I have a list of products and few of those products are new to the hierarchy with no history. In this case i need to compare and refer from SALES line item' of a different product from same list. 

 

  • I have only 1 product hierarchy list to use and it has associated sales, cost, profits,etc with it.
  • Few Product has similar names(ex: Manchester jersey, Madrid Jersey, etc)
  • I can compare using NAMES and lookup at their sales

                            or 

  • I can also compare SALES with other products and refer to that respective SALES line item.
  • I tried to use lookup function and use a condition but it did not work

 

What formulas can i use here to refer to Sales of other products in the same hierarchy and at same level of hierarchy??

 

 

Thank you!

Tagged:

Answers

  • PaulRitner
    PaulRitner

    Hi Sai,

     

    If I understand correctly, when you get new products with no history, you align the new product with (similar) existing products to product trend or other comparative analysis.

     

    It might be possible to relate a new product (with no history) to another product via a mapping.

     

    For example, each time you add a new product, you can map (one or more old products) to it for comparison purposes.  This can be done in a module based on the product list.  Further, you can effective date the mapping so that it only applies to certain periods, or prior to some date, etc.  The formulas for monthly fact data might ask questions like (in pseudocode):  If Product_Has_Map then get map's data, else use product's data.  or... If Product_Has_Map and Start()<Effective_End_Date then get map's data, else use product's data.

     

    Depending on how the product hierarhcy is set up, you might be able to leverage a parent to indicate the comparison product... or view information at an aggregated level.  You could also group & average related product family/category activity.

     

    There might be opportunities with product codes to derive the correct existing product to compare to, but the stars would have to align really well for that. 

     

    Not sure if this helps.  Cheers!

     

    Paul

     

  • saibandaru
    saibandaru

    Thanks for your view Paul. 

     

     

    There is no problem in adding the product to the respective hierarchy/list. I dont get what formulas to use in the module so as to map one product data with another product within the same hierarchy and same level. 

  • PaulRitner
    PaulRitner

    the formulas could his take a few steps, but not too bad. Might look like this:

     

    Module 1 Name:  Product History Mappings

    Module 1 Lists: Products, no Time, possibly versions

    Module 1 Line Items: 

    Default Product Map (format to products, default to the current product)

    History Map Override (formatted to products, Manually align new products with old products)

    Product Map Override Effective End Date (format as Month, indicate period where history should be overlayed through)

     

    Module 2 Name:  Product Activity

    Lists:  Products, Time, Versions

    Line Item:  Amount (number format), formula is something like this: IF  Product History Mappings.Product Map Override Effective End Date < Start() THEN Product_Data.Amount[sum: Product History Mappings.Default Product Map] ELSE Product_Data.Amount[sum: Product History Mappings.History Map Override] 

     

    Note that Module 2 historical activity may have to be further adjusted so that it aggregates correctly.  This could take many forms, but depends on how the output is being used (may or may not be relevant).

     

     

     

Categories