Predicting Forecast numbers with Previous values?

[kiotsuresh]

I have Data Like below, Data Till 202210 will be considered as actual and data from 202211 will be considered as future. We need to achieve The Future Data  using the given logic.

Glimpse of the logic is : Consider the month is 202211 = (1+Oct 2022)*((Nov 2021-Oct 2021)/(Oct 2021)) and for other months The months change accordingly, I need to Achieve this forecast till 2028( I have Actual Data Till 202210). How to achieve this through Alteryx?

Scenario	Item	Period	Group	Name	Amt
Actual	Chair	202109	Group 1	Sales %	4.6%
Actual	Chair	202110	Group 1	Sales %	5.5%
Actual	Chair	202111	Group 1	Sales %	6.0%
Actual	Chair	202112	Group 1	Sales %	5.6%
Actual	Chair	202201	Group 1	Sales %	6.5%
Actual	Chair	202202	Group 1	Sales %	4.5%
Actual	Chair	202203	Group 1	Sales %	6.2%
Actual	Chair	202204	Group 1	Sales %	4.2%
Actual	Chair	202205	Group 1	Sales %	5.2%
Actual	Chair	202206	Group 1	Sales %	4.5%
Actual	Chair	202207	Group 1	Sales %	5.5%
Actual	Chair	202208	Group 1	Sales %	5.4%
Actual	Chair	202209	Group 1	Sales %	6.2%
Actual	Chair	202210	Group 1	Sales %	7.2%	LOGIC:
Future	Chair	202211	Group 1	Sales %		Nov 2022 (For Future Month)	(1+Oct 2022)*((Nov 2021-Oct 2021)/Oct 2021)	10%
Future	Chair	202212	Group 1	Sales %		Dec 2022 (For Future Month)	(1+Nov 2022)*((Dec 2021-Nov 2021)/Nov 2021)
Future	Chair	202301	Group 1	Sales %		Jan 2023 (For Future Month)	(1+Dec 2022)*((Jan 2021-Dec 2021)/Dec 2021)

[MatthewO]

@kiotsuresh you can accomplish this using a Multi-Row Formula  tool. I have attached an example.

[kiotsuresh]

The Data I'm Having is Huge. Row 13 here represents the total actual rows right? It will be a dynamic one. And I want this to automatically run and execute in future also.

[MatthewO]

@kiotsuresh the Multi-Row Formula is a dynamic tool. From the current row being evaluated, it will look back or forward the defined number of rows. The logic for looking back 12 and 13 rows is to capture the Amt value from 12 and 13 months ago from the preceding rows. The expression assumes two things 1) that there is a row for every month in the period and 2) that the data is sorted from the oldest date to the newest.

In the screenshot below, I added 11 new sample periods to demonstrate the behavior. When the expression is evaluated at Sample Period 11, the current Amt, and the Amt from 12 and 13 periods ago are all 0. A Null value is returned because the expression would be dividing by zero in period 11. However, preceding periods return a result.

[PhilipL]

Hi @kiotsuresh,

You may also want to explore solving this with the Time Series predictive tools, especially if you have a large amount of historic data available to use.

[kiotsuresh]

Attaching the Exact Format of the sample data that I'm Trying to achieve this solution. This may give an clear Idea. I have Actual and forecast data available for Sales till 202812, but for other Line items It is available till 202210, so I need to achieve the logic for these line items  and put under the Forecast Scenario. 

Damage

Profit

Revenue

Discount

Giveaway

I'm Trying multiple scenarios using multi row formula and not working out.

This Calculation need to be executed for each and every period group. say for example if we are forecasting Profit for 202211 - the output of the logic should have 13 records by period group like below,

Scenario	Accounts	Period	Period Group	Client	Value
Forecast	Profit	202211	Year Before2011	Amazon	100
Forecast	Profit	202211	Year 2011	Amazon	101
Forecast	Profit	202211	Year 2012	Amazon	102
Forecast	Profit	202211	Year 2013	Amazon	103
Forecast	Profit	202211	Year 2014	Amazon	104
Forecast	Profit	202211	Year 2015	Amazon	105
Forecast	Profit	202211	Year 2016	Amazon	106
Forecast	Profit	202211	Year 2017	Amazon	107
Forecast	Profit	202211	Year 2018	Amazon	108
Forecast	Profit	202211	Year 2019	Amazon	109
Forecast	Profit	202211	Year 2020	Amazon	110
Forecast	Profit	202211	Year 2021	Amazon	111
Forecast	Profit	202211	Year 2022	Amazon	112

[kiotsuresh]

@MatthewO the values present in the table snippet are the sample ones i have pasted for reference.

[MatthewO]

@kiotsuresh I've looked through the new data set but I'm not completely understanding the objective. When forecasting, historic values are used to predict future values, but it's not clear which historic values need to be considered for each period. For example, in the Profit accounts, this seems to suggest different actual performance values in May 2019.

If the future periods that you want to populate do not exist in your data set, you can generate these using the Generate Rows tool, then populate their value with the Multi-Row Formula. 

[kiotsuresh]

Hi @MatthewO 

The main date which we need to consider in the data set is the period column. we need to achieve the forecasting using the historic period with the formula. Period group is just a sub category under each period.