Moving numbers from one column to the next

Hi please refer screenshot below. I am trying to move the last number in the column highlighted in red (Redistribution Ratio) to the next column, Finalized Redistribution Ratio. There are 20 rows in the highlighted part of the column. Redistribution ratios will automatically recalculate for each country, formula already working. The number to be copied to the adjacent column is 0.9912 which appears on the last row of the highlighted box in red. The Finalized Redistribution Ratio column should populate in such a way the number to be copied over i.e. 0.9912 appears 20x corresponding to each row in the highlighted box to its left.

Question is how does one do this? Am thinking solution would be to have a number column in the module that ranges from 1 to N where N is the number of rows in the module. As each number is unique we should then be able to use it as an index for a lookup as the intersection of that number and the Redistribution Ratio column would be the number we're looking for and then we would just move that number to the column on its right, the Finalized Redistribution Ratio column. Of course we would then have to populate it in the manner described above.

I know this sounds a bit complicated but there must be a solution for this so appreciate your ideas!

TAR02 Account Review 210507_1.jpg

Kind regards,

Clarence

Accepted answers

ClarenceAndre_*44012

Hi @ChrisAHeathcote

There might be better solutions but I finally managed to solve by taking the following actions:

1. Creating a SYS module with a Rank Flat list as dimension containing all the Redistribution ratios in the module;

2. Setting up the lookups I needed within the TAR03 module;

3. Also within the TAR03 module looking up the Redistribution ratio in the SYS module referred in 1. above.

Correct me if wrong but what I learnt was for the lookup to work I first needed to convert numeric row references / indices to a ranked list format and then using the list format to lookup the ratio needed in the SYS module.

Kind regards,

Clarence

ChrisAHeathcote

@ClarenceAndre

You are indeed correct in that a LOOKUP will only work when the lookup line item is a list formatted item.

However, you could use MIN summary method and then SELECT to reference the top level of the product list.

This may have been a simpler solution!

All comments

ChrisAHeathcote

@ClarenceAndre

Create a calculation module where you can run the calculation without the row dimension.

You can then reference the calculation line item and it will apply across all list items in the target module.

ClarenceAndre_*44012

Hi @ChrisAHeathcote

There might be better solutions but I finally managed to solve by taking the following actions:

1. Creating a SYS module with a Rank Flat list as dimension containing all the Redistribution ratios in the module;

2. Setting up the lookups I needed within the TAR03 module;

3. Also within the TAR03 module looking up the Redistribution ratio in the SYS module referred in 1. above.

Kind regards,

Clarence

ChrisAHeathcote

@ClarenceAndre

You are indeed correct in that a LOOKUP will only work when the lookup line item is a list formatted item.

However, you could use MIN summary method and then SELECT to reference the top level of the product list.

This may have been a simpler solution!

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…