How to do top-down allocation of an integer value

Center of Excellence · March 4

1. Abstract

The article explains a possible method to ensure that top-down allocation of an integer value generates integer details through the redistribution of decimals.

2. Content

In this example, we have a cube QTY [Year, Customer, Product] which is planned at a high level [Year, Product, Country] where Country is the parent of the customer.

If for example, we use Split&Splat functionality at [Year, Product, Country] Level, we have existing mix-based allocation and this inevitably generates decimal numbers.

2.1 Dataview configuration

Create a new Dataview
- ROWS: Product, Country, and Customer
- Block A: Cube Qty

2.2 Nexel

Follow the next steps to configure Nexel.

Step 1: Eliminate all the decimals:

Step 2: Isolate decimals:

Click on +BLOCK
Set the block as follow:
- Decimal
- Single Rule Formula
Click on the column and type the formula=[@a;*;*]-[@b;*;*]

Result:

Step 3: Duplicate for every customer delta between Qty and Trunc Qty at Product-Country level

Click on +BLOCK
Set the block as follow:
- Delta
- Single Rule Formula
Click on the column and type the formula= round(SUM([@c;*;*;Range.Vertical]),0) [@a;*;*]-[@b;*;*]

Result:

Step 4: Ranking block using Counter

After calculating decimals, we need to rank the decimal value. Using the rank function if there are two or more cells with the same value, the number of the rank is the same.

For example: Customer 9 and Customer 10 would have the same rank value. We must have a unique ordering. To do that, use a Counter to create a unique rank value.

Open the Dataview and set the ranking formula: Counter

Result:

Create a column algorithm = Counter/1000 - To have a unique thousandth value

Result:

Step 5: Create a unique rank

Click on +BLOCK
Set the block as follow:
- Rank Decimal + Counter/1000
- Single Rule Formula
Click on the column and type the formula= rank([@c;*;*],[@c;*;*;Range.Vertical])+[@f;*;*]

Result:

Step 6: Create a unique rank without decimal

Click on +BLOCK
Set the block as follow:
- Rank New
- Single Rule Formula
Click on the column and type the formula= rank([@g;*;*],[@g;*;*;Range.Vertical],1)

Result:

Step 7: Assign truncated decimals: find which elements to assign the truncated decimals.

If [Rank new] <= [Delta] then 1 else 0.

Click on +BLOCK
Set the block as follow:
- Delta
- Single Rule Formula
Click on the column and type the formula= if([@h;*;*]<=[@d;*;*],1,0)

Result:

Step 8: Sum the trenched value with the Delta

Click on +BLOCK
Set the block as follow:
- QTY Rounded
- Single Rule Formula
Click on the column and type the formula =[@b;*;*]+[@i;*;*]

Result:

Step 9: Save NEXEL: Save the layout created with all the formulas explained above.

2.3 Procedure Configuration

Create a Procedure with the step Nexel writeback - “Round QTY”.

3. Conclusion

This is a method to round values based on a rule that favors items that have decimals closest to the integer.

Important note: It's essential to save the Dataview with Nexel configured in a screen, else it won't be possible to retrieve the configuration done at the layout level.