Comparing the sales line item of a similar kind of product

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

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!

Accepted answers

All comments

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

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

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).

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…