Returning Dimension/List Item based on Line Item

MarkTurkenburg_*8288
MarkTurkenburg_*8288
in Modeling

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

Tagged:

Best Answers

  • ArunManickam
    ArunManickam
    Answer ✓

    See if this helps..

     

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

     

    image.png

  • anirudh
    anirudh
    Answer ✓

    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!

     

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

    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

Answers

Categories