Replacing Forecast columns with Actual Data

Question

Hi all,

I have an Excel file with forecast columns Jan_2025_LC, Feb_2025_LC, Mar_2025_LC, ... and corresponding actuals: Actual_Jan_2025_LC, Actual_Feb_2025_LC, Actual_Mar_2025_LC, etc.(Sample data attached) (This pattern repeats for each year, e.g., 2025, 2026, etc.)

I want to build an Alteryx workflow where, if I select a run period (for example, Mar-25), the workflow will automatically replace the values in Jan_2025_LC, Feb_2025_LC, and Mar_2025_LC with the values from Actual_Jan_2025_LC, Actual_Feb_2025_LC, and Actual_Mar_2025_LC. For months after the run period, the forecast values should remain unchanged.

I also want this logic to work dynamically for any year (2025, 2026, etc.), not just for 2025.

What’s the best way to set this up in Alteryx Designer?
Any tips, sample workflows would be greatly appreciated!

sample data.xlsx

Pilsner · Accepted Answer

Hello @duckduck2001,

I believe I have created a basic app that functions as you expected. The idea is the user selects a date from a dropdown:

Then, after simply hitting finish, you should be presented with a table that reflects the updates (I selected "Actual_Feb_2025_LC"):

The way this app works is as follows:

1) Admittedly, I wasn't sure what you wanted to do with the total columns& USD columns, so I dropped them at the start. If you did want to add these back in without changing there values, it would simply be a join tool at the end of the workflow.

2) The next step was to pivot the data, this listed all the month/year values in one column, and their corresponding values / forecasts in another.

3) Now we have the data formatted like this, we need to separate out the "actual" columns, from the forecasts. This can be done with a filter. I would then recommend, extracting the date out from the original column headers, so we can easily filter to the ones we want to update.

4) With the dates extracted, we now want to filter to the user selected dates, from the "actual data". I used a filter tool to isolate the relevant dates, and an interface tool to update the user selected period.

5) With the correct dates selected, we now need to remove them from the list of forecasted values. We can do this using a join tool. From the J anchor, we want to keep the actual values, then all the remaining, unaffected forecasted values, should appear on the R anchor.

6) If we union these two streams back together, along with all the "actual values" we will have the fully updates list of value's. By pivoting the data back around, and unioning the headers from the original input data, the output should be created.

I have attached the annotated worklfow below to try and assist further. Please let me know how you get on.

Regards - Pilsner