Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Comparing actuals to forecast (period - 3)

8 - Asteroid

Hi All,


I need to be able to compare actuals data with the forecast data for the same day based on the forecast 3 months prior to the actual date.  Eg: The February actuals need to be compared to the February forecast as determined in november.  Any thoughts on how this could be done dynamically based on the actuals period?


As always, I appreciate your collective help and wisdom.




Alteryx Alumni (Retired)

Depending on your data I can think of a couple different ways to get your data lined up...


If in November you actually had a specific date in February forecasted (i.e., on November 20 you forecasted what the actual would be for February 19), then you could take that forecast with the forecast date, split it out into a data flow, and then join it back with the forecasted date joined to the actual date. Then it is an easy formula to compare the amounts.


If it is more of a "relative date" situation, where you want to see what the forecast was 90 days ago, you could use a multi-row formula tool with the "Num Rows" set to 90 and use the expression "Row-90:ForecastValue" created in a new field. (Of course, if you have records for only business days, you would want to adjust that to Row-60 or something like that.) Then you again have an easy formula to compare the amounts.


Alteryx Certified Partner
Alteryx Certified Partner

With the data transposed into key value pairs, I could lookup DateTimeAdd([Current Date Field],-90,"DAYS") in a Join to the data and capture the FORECAST variable.  


Would this work for you?

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
ACE Emeritus
ACE Emeritus

The attached workflow will generate a forecasts based on a 12 month period (up through 3 months ago), and compare the results to actuals from last month; also for a 365 period through 90 days ago, then forecast for 90 days with comparison.


The numbers passed in are just randomly generated, but the basic process should also work on real data.  Hope it helps!