Returning Dimension/List Item based on Line Item

Contributor

Returning Dimension/List Item based on Line Item

Hi,

 

I'm trying to understand whether I can return a Dimension/List item when referencing a Line item rather than the Line Item result itself

 

I have 2 modules. 1st module is dimensionalised by FX Rates (columns) & Regions (List Formatted Line Item)FX Mapping Module.PNG

 

The 2nd module has a Line Item which is List Formatted with FX rates & dimensionalised by Regions. 

 

Module 2.PNG

 

I want to be able to return the FX Rate for the each region dimension. I thought FINDITEM(FX Rates,FX Mapping.'Region (Text)') but this doesn't work as I need to return the List item from the Line item, not the line item result itself

 

I am aware it is easier to build the FX rates module with Regions as a dimension and then FX Rates as the List Formatted line item for future references but this problem has me intrigued

 

Many thanks

 

Mark

Message 1 of 7
6 REPLIES 6
Frequent Contributor

Re: Returning Dimension/List Item based on Line Item

See if this helps..

 

You need to cross the two dimensions to achieve this..

 

image.png

Message 2 of 7
Certified Master Anaplanner

Re: Returning Dimension/List Item based on Line Item

Hi Mark,

 

This answer should not be implemented and you're right that it's easier to build the FX Rates module with the Region dimension.

However, you can achieve this by using the TEXTLIST formula (works exactly like SUM formula but for text)

 

In the first module, create a text formatted line item called FX with the formula as NAME(ITEM(FX List))

 

image.png

In the second module, create a text formatted line item called FX with the formula: Module 1.FX[TEXTLIST: Module 1.Region]

 

image.png

 

This should work!

 

Message 3 of 7
Certified Master Anaplanner

Re: Returning Dimension/List Item based on Line Item

If you are sure that there is only one result per region, then Anirudh's method with FIRSTNONBLANK should perform better I think.
Message 4 of 7
Community Boss

Re: Returning Dimension/List Item based on Line Item

For a simple currency conversion to the "base" currency, the dimensionality of most of the answers above are the wrong way around

 

1. You have a module by Currency code that contains the rates (against the base currency)

2. You have a module by region with a formatted line item, formatted as Currency code

3. In the target module you can bring the regional FX rate by using a LOOKUP

e.g, Currency Calc.Converted = Local / FX Rates.Rate[LOOKUP: Region Details.Currency Code]

I've assumed GBP is the base currency (hence the "1") in the rate

2019-07-11_18-42-37.png

 

2019-07-11_18-42-56.png

 

2019-07-11_18-43-22.png

 

If you want to return the "cross rate" calculations showing the local currency in different currencies, then you can perform the calculation in the target module (with Currency Codes as a dimension), by using the formula above with a * or / against the currency code list itself

e.g. Currency Calc.Converted * FX Rates.Rate

2019-07-11_18-43-49.png

 

By the way, be wary of using Textlist. It is a very heavy calculation and can cause performance issues when used on large lists with a lot of text to summarise

I hope this helps

David

Message 5 of 7
Contributor

Re: Returning Dimension/List Item based on Line Item

Thanks David for the detailed reply!
Message 6 of 7
Highlighted
Contributor

Re: Returning Dimension/List Item based on Line Item

Thanks!
Message 7 of 7