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

Accepted answers

ArunManickam

See if this helps..

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

anirudh

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))

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

This should work!

bram.kurstjens

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

DavidSmith

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

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

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

All comments