Translating Values to Current Rates

[Arkouda]

Hi All 

I'm trying to create a work flow that validates the FX rates of all periods by taking the underlying input currency and multiply them by the FX rate table per month. The recalculation (EURO Value in the Input 19A * Euro Rate) and it should calculate the same for all the input currencies. At the end all the calculations should equal to the highlighted row by period USD @ Forecast Rate. I'm trying to validate to make sure that the rates in the system are calculating correcting - see the two attached file 

1) first file is the input file by period 

2) is the FX Table by period 

I started something, but it's easy to do in Excel but I want to add this in Alteryx 

Thank you

[T_Willins]

HI @Arkouda,

We use similar workflows as part of our trial balance processes.  Attached is an example using your data.  The easiest way to to compare data with multiple months is to first Transpose the data values into a single column so a Join can be used to put the FX rates on the same row as the input values by month and currency.  The rest of the workflow uses a Formula tool to calculate USD amounts, Sum USD by month, Join with the USD_Forecast data and finally a Formula tool to calculate the variance.  There is a variance showing as Excel limits the amounts in a calculated cell to 15 significant figures, while Alteryx does not have this limitation*.

* There is a single record size limit of 2GB in a 64-bit environment, but the number would have to be very, very large!

[Arkouda]

Hi T_Willins

Thank you for the quick response on this. Very helpful, but I have a wrench to throw this into - I have multiple entities that I need this to work on. See attached the actual work file - I attempted on my end to add this file, but running into issues 

Thank you 

[T_Willins · Accepted solution]

Hi @Arkouda,

This should now work.  Using the original file I was able to eliminate the data cleansing.  Added a group by entity to the Summarize tool so the last Join can use entity and month.

[BenMoss · Accepted solution]

Hi!

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

Learn more about Accepted Solutions here.

Thank you!

[Metadata21]

Hi T Willins 

I just wanted to ask where I could get an updated version of your FX table rates for 2022?