Non Parent/Top Item List Relationship

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?

Input Module.PNG Calculation Module.PNG

Accepted answers

All comments

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

Quick Links

Can you help?

Unanswered questions

Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…