Non Parent/Top Item List Relationship

MarkTurkenburg_*8288

Hi team,

 

I have a Currency list that has no parent/top item & an input module whereby the user will enter the data into the Currency dimension. In a secondary module I want to convert all those currency values entered by the user into 1 currency. I can only get this to work at the moment by not having a currency in the 2nd module/having a subset of the currency list item I require & creating a manual formula whereby I select each currency type & converting it to the required currency (again by using a select item). I'm aware that this is not  a scaleable solution & breaks some of the best practices but I'm having difficulty in finding a more dynamic solution, does anyone know a more robust manner to reference items within the same list that do not have a parent?

 

 

 

kavinkumar

HI @MarkTurkenburg,

PFB,

1. Why can't we add 'Top level' item to the 'currency list' in this case and make the solution more scalable?

2. I have gone thru the formula and we can reduce the system time to calculate by writing the same formula in this way. [(a+b+c+d)/z] instead of [a/z+b/z+c/z+d/z].

3. To avoid Select function, please use 'SYS Lookup' module. ie, Create a module with no dimension and create no of line items as per your need and make them 'Currency list' formatted and do manual selection as one time activity and refer these line items using Look up function. Ex: Deffered Incentives Amoratisation - Input.Deferred Incentives[Lookup:SYS Look.Line item 1]+ and so on. This would help you to mark the list as Production data.

I hope this helps!

Regards,

Kavin.

MarkTurkenburg_*8288

Hi @kavinkumar .

 

Thanks for the quick reply

 

1) Can you help & explain how the top level would work? Even if you had a top level 'All Currencies' & then you had a subset of the currency list in the calculation module, say AUD, when you referenced the original module it would return the sum of all the currencies in their nominal/input value which is not a useful. The nominal/input values need to be translated from the input currency to AUD & then Summed 

 

2) Don't think this calculation works as each Select statement that is performed to obtain a particular currency value needs to be translated using another select statement (again the currency). There is no overarching value or relationship that I can see that we drive a dimensional value return

 

Thanks!

 

Mark

nathan_rudman

Hi,

 

The way to avoid this long formula here is, in my opnion, to make the conversion first of every element. This way every currency can pull its own FX rate and you don't even need a LOOKUP. Then you can add the converted amount into your final line item.

You can do that in the final module you have with line item subsidiary view with Currency as additional dimension.

 

You'll need a top level for that.

DavidSmith

@MarkTurkenburg 

You can do this with a simple staging module

1. Assign currency codes to Regions

2. Add FX rates (I have assumed the base to be GBP)

3. Create a module by Region and Time to pull in the FX rate.  you need to do this to prevent the target module ignoring the LOOKUP (We found this as a recent bug!!)

4. Now you can use the rates from 2. and 3. to converted the input values

I've broken up the line items for visibility of the calculation, but you can combine them obviously

Global Base Rate = FX Rates.Rate[LOOKUP: Region Details.Currency]

Region Rate = Region Rates by Year.Rate

Converted = Local / Global Base Rate * Region Rate

Converted Combined = Local / FX Rates.Rate[LOOKUP: Region Details.Currency] * Region Rates by Year.Rate

 

5. You can then view the totals by Currency for all Regions

 

 I hope that helps

David

MarkTurkenburg_*8288

Thanks heaps @DavidSmith , @nathan_rudman  & @kavinkumar  for the detailed explanations!

 

I'll work through it and revert back if any issues

 

Mark