Multi-Field formula tool help and how to sum fixed range of fields?

[wendellgu]

Hi, I am new to Alteryx and this is my first time asking for help in the community. I have this monthly report that I need to convert the local currency amount to the USD amount using monthly provided exchange rates in the new fields.  I also need to sum the 1st twelve month USD amount, and sum the USD amount for periods after the 1st twelve month periods.  

The sample data file is attached here. The monthly reports are in separate tabs. The monthly report data (the input data) are in Column A to column BD. The desire results are in column BF to column EV.

Goal 1: Create new fields and converted the monthly amount, yearly amount, Total and Grand Total amount in local currency (yellow highlighted column J to BD ) to USD amount, using the Forecast Exchange Rate (the desired results are in the Excel file, in column BF to column CY,  blue highlighted columns)

Goal 2: Create new fields and converted the monthly amount, yearly amount, Total and Grand Total amount in local currency (yellow highlighted column J to BD ) to USD amount, using the Actual Exchange Rate (the desired results are in the Excel file, in column DA to column ET, green highlighted columns)

Goal 3: Create two new fields, first field to sum the first 12 month period USD amounts from Goal 2, then 2nd field to sum the remaining period USD amounts from Goal 2.  (the desired results are in the Excel file, in column EU and column EV, purple highlighted columns, )

Note: I have tried using the Multi-Field formula but sometimes the monthly and yearly data come in as V_WSTRING type, and do not show up in numeric field selection in the Multi-Field formula tool and cannot be used for calculation.  Every time I run the workflow with a different month report, I have to use the Select tool to check the fields' data type, make changes if necessary to ensure the calculated fields (monthly, yearly, total and grand total fields) are in numeric data type so I can calculate in Multi-Field formula tools. I also have to make sure all the calculated fields are checked, and the non-calculated numeric fields are unchecked in the Multi-Field formula tool.  For example, newly created fields in Goal 1 are in numeric data type, these fields will appear in the 2nd Multi-Field formula tool in Goal 2, therefore these fields need to be unchecked.

I would like to know if  there is a way to set up the workflow that can eliminate the manual checking and changing the data type to numeric for the calculated fields, manual checking the calculated field and uncheck non-calculated numeric fields in the Multi-Field formula tool?  Maybe there is another Alteryx tool that can be used here besides the Multi-Field formula tool.  I just want to automate the workflow so I can load the input file, run the workflow and get the output file.  Can anyone help here? Thanks in advance.

 Apr'23 Sample Data and Result

May'23 Sample Data and Result

Jun'23 Sample Data and Result

 

[jdminton]

Hi @wendellgu A couple of things I'm noting before my explanation:

1. Looking at the data, the field names are changing each month.
2. Also, some amounts are showing as string in June file, but not the others

Because of this, you will need to do a couple of things before your multifield tool. Using a input and dynamic input tool, you will bring in the sheet names and feed into the dynamic input. When the data is brought in, you will need to treat the header row as data. (see images)

 

  

This will work as long as the data is in the same order. You may consider changing the column headers with Current month, month +1, month +2, etc. in your workflow since the information depends on the column. Once the data is in, you can use a single select tool to force the strings to double. The multifield tool can be used to select all amounts then for your formula. You may have to do some different things depending on how you are using the data and what types of formulas you're trying to create, but this will get the data in and format it. This probably won't answer all of your questions, but should at least help you progress a bit. Share your workflow once you've updated so we can help more.