How to get the nth occurrence of an item using the same list

DavideVitale

Dear Community,

 

Hope you can help me with this requirement, this is the scenario: there is a list of products and a dedicated module that allows the user to select the replacement product from the same list.

A product can be replaced by one product only, while any product can replace multiple existing products.

 

Referring to the attached image, do you know how to populate the module on the right? Specifically, I would like Anaplan to return which substituting products replace the related existing products, based on the first module on the left (1st occurrence, 2nd occurrence, nth occurrence, etc.). This module is dimensioned by an additional list of 'n' items (Generic Products).

Would it be feasible to get this result through some calculations and additional line items? Do you suggest any other method?

 

Thank you

Davide

JaredDolich

@DavideVitale 

Great use case. Happens all the time in Retail. We sometimes refer to this scenario as a recursive join.

There are a couple of ways you might approach this but it really depends on the use case. Can you share a little bit more on how you intend to use this?

Some ideas:

1. Did you consider creating a structured hierarchy? Since an item can only have one replacement, then you have a natural parent-child relationship. You can use filters to only show those items that have replacements (the parent, as shown in your second chart).
2. Separate the lists (2 flat lists) so you have separate dimensions, one for rows, one for columns. Again use filters to only show items that are used as replacements.
3. Use the SUM function on the list formatted line item of the items that have replacements. The Target module would use a list, or list subset, of the items that have replacements.