Hi ​@Apurv ,

A possible solution to achieve your goal is to create a technical dimension that helps simulate the loop logic. Here's a detailed step-by-step breakdown:

Step 1: Create a Technical Dimension

Start by creating a dimension (e.g., ID10) that has the same number of items as your Year dimension. This will be used to simulate the loop.

Define the following properties:

• Name: Integer — generated automatically by Pigment.

• Reverse ID: Integer — calculated with this formula:

  ID10.Name1REMOVE MAX: ID10] - ID10.Name + 1
  

  This flips the rank of each item to get the reverse order.
   

  

   

Step 2: Create a Matching Metric (Rank A)

Build a metric Rank A (data type: dimension ID10) that maps each Year item to a corresponding item in the ID10 dimension using the following formula:

MATCH(
  Cumulate(IFDEFINED(A, 1), Year),
  ID10.Name
)

This links each year's cumulative rank to an item in the ID10 dimension.
 

Step 3: Define the Multiplier Matrix

Next, define a multiplier matrix that determines the loop behavior across ranks in reverse order:

IFDEFINED(AFBY: -> 'Rank A'], 1, PREVIOUS(ID10 ON ID10.'Reverse ID') + 1)
+FILTER: ISDEFINED(A)]

Explanation:

• The formula initializes with 1 where the rank matches.

• Then it uses the PREVIOUS function across ID10 in reverse order.

• The filter ensures only defined values of A are considered.
   

  

   

Step 4: Apply the Multiplier in the Final Metric

To apply the multiplier and finalize your logic, use the following formula in your B- formula metric:

(ApBY: 'Rank A'] * 'Multiplier Matrix')
tREMOVE: ID10]

Explanation:

• The pBY: 'Rank A'] switches the values of A from the Yearto the ID10 dimension.

• This allows proper alignment with the multiplier values.

• Finally, iREMOVE: ID10] aggregates the result back to the Year dimension.
   

  

   

Hope this helps!

Hi ​@Issam Moalla ,

Thanks for the help, I tried it and it works perfectly.

Thanks

Apurv