How to pickup value from a cell and populate next column

ClarenceAndre_*44012

Kindly refer attached screenshot. Am trying to figure how does one populate the boxed column with the value in the cell (1,414). I am already able to do something by looking up a SYS table I constructed but the problem is I can't seem to figure out the right formula to use which should be to pick up the value 1,414 which corresponds to Rank 20 in the Rank column.

Any ideas much appreciated.

Kind regards,

Clarence 

 

 

ArunManickam

Hello @ClarenceAndre ,

 

You can do by the following construct.

 

Convert your Rank (Number) to a Rank (list)

Build module by Rank (list) and have your source line item there. (it can be a subsidiary view in the same module also)

source line item[select:rank.20] or you can use a lookup to avoid SELECT / hard coding

 

Thanks

Arun

ChrisAHeathcote

@ClarenceAndre 

I agree with @ArunManickam but instead of creating a ranking list can you not create a LOOKUP where you direct the query to lookup the list item in the current module which corresponds to rank 20.

 

I will update the comment once I have developed a solution to inform  the LOOKUP.

 

ChrisAHeathcote

If the product list contains a top level then create a new line item in which you return the value only if the rank = 20.

Set the summary to LASTNONBLANK. 

The use SELECT against the top level item to pull the value into the adjacent column.

That should do it!!

ClarenceAndre_*44012

Many thanks Arun, let me try that out.
Best regards,
Clarence

ClarenceAndre_*44012

Hi Jennifer,
Were you referring to a formula in the email chain or something you had intended to paste into the body of your email?
Best regards,
Clarence

rob_marshall

@ClarenceAndre 

 

Hey, Jennifers "reply" was spam, so it is now gone.  Just wanted you to know so you didn't think something just disappeared on you.

ClarenceAndre_*44012

Hi @ChrisAHeathcote 

I like the idea but if am not wrong SELECT works with fixed parameters so if the number is not fixed at 20 it may not work? The number corresponds to number of accounts per selected country which varies from country to country. I am able to derive the number by indicating in a column the number of accounts I intend to modify. In the example given it was 20 but for another country, it may be a different number like 15. Given the above, is SELECT the right approach?

As you had said LOOKUP will work but am not sure how to do that within the same module or is the idea to have the number (say 20) picked up by a SYS module and to have a LOOKUP within the module referencing the SYS module for the value I need?

Kind regards,

Clarence 

ClarenceAndre_*44012

Hi @rob_marshall thanks! It did sound a little weird but thanks for picking that up!

Kind regards,

Clarence

ChrisAHeathcote

@ClarenceAndre 

Do not use the SELECT to against twenty but the top level item of the dimension show in your screenshot.

You need to create an extra line item. Then create a query which only pulls through a value which corresponds to the rank column.

As this differs by country you will need a system module to feed into this IF THEN ELSE.

It would look something like this;

=IF Rank=(rank number from system module) THEN value ELSE 0

As a result the new column should only contain one value which corresponds to the row which contains the rank number as defined in the systems module. 

Set the summary method for this line item to LASTNONBLANK then use SELECT in your target line item to return the top level value and distribute it to all lower levels.

If you need the adjustment to only apply to list items with a rank value then use a IF THEN ELSE to only use the SELECT for list item that have a rank value greater than zero. 

I hope this clarifies.

ClarenceAndre_*44012

Thanks Chris for the reply, sounds bit complicated but let me work through the logic and see if I get it.