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? ScenarioItemPeriodGroupNameAmt ActualChair202109Group 1Sales %4.6% ActualChair202110Group 1Sales %5.5% ActualChair202111Group 1Sales %6.0% ActualChair202112Group 1Sales %5.6% ActualChair202201Group 1Sales %6.5% ActualChair202202Group 1Sales %4.5% ActualChair202203Group 1Sales %6.2% ActualChair202204Group 1Sales %4.2% ActualChair202205Group 1Sales %5.2% ActualChair202206Group 1Sales %4.5% ActualChair202207Group 1Sales %5.5% ActualChair202208Group 1Sales %5.4% ActualChair202209Group 1Sales %6.2% ActualChair202210Group 1Sales %7.2%LOGIC: FutureChair202211Group 1Sales % Nov 2022 (For Future Month)(1+Oct 2022)*((Nov 2021-Oct 2021)/Oct 2021)10%FutureChair202212Group 1Sales % Dec 2022 (For Future Month)(1+Nov 2022)*((Dec 2021-Nov 2021)/Nov 2021) FutureChair202301Group 1Sales % Jan 2023 (For Future Month)(1+Dec 2022)*((Jan 2021-Dec 2021)/Dec 2021)

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

Predicting Forecast numbers with Previous values?

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)

Datasets

Developer

Designer Cloud

Connectors

Date Time

Transformation

Predictive Analysis

Interface Tools

Data Investigation

Accepted answers

All comments

MatthewO

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

Multi Row Formula.yxzp

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

Alteryx Prediction Sample.xlsx

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703