Calculating the future month values using the historical month values.

[kiotsuresh]

I have a Dataset as attached, I have Actuals till 202210 for all line items and forecast values till 202812 for sales alone. I need tp calculate the forecast numbers for all other line items which are all ,

Damage

Profit

Revenue

Discount

Giveaway

using the below formula : ex for 202211 - (Nov2022 value)*(1+(Nov 2021value -Oct 2021value)/(Oct 2021value)) , This calculation should be applied to predict the values till 2028.

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
Forecast	Profit	202211	Year 2011	Amazon
Forecast	Profit	202211	Year 2012	Amazon
Forecast	Profit	202211	Year 2013	Amazon
Forecast	Profit	202211	Year 2014	Amazon
Forecast	Profit	202211	Year 2015	Amazon
Forecast	Profit	202211	Year 2016	Amazon
Forecast	Profit	202211	Year 2017	Amazon
Forecast	Profit	202211	Year 2018	Amazon
Forecast	Profit	202211	Year 2019	Amazon
Forecast	Profit	202211	Year 2020	Amazon
Forecast	Profit	202211	Year 2021	Amazon
Forecast	Profit	202211	Year 2022	Amazon

@ShankerV @MatthewO 

[AndrewSu]

@kiotsuresh , could you help me understand the dataset a bit better?

What is the relationship between the "Period" and the "Period Group"?

Looking at your formula  (Nov2022 value)*(1+(Nov 2021value -Oct 2021value)/(Oct 2021value)) it's not clear to me what cell/value refers to these variables since it looks like the "Period Groups" are appended to every "Period". 

Asked in another way, when you refer to the "Oct 2021value" which cell is that referring to and why?  see screenshot below. 

Having this clarity will help me assist you more effectively. 

Thanks. 

[kiotsuresh]

@AndrewSu 

Thanks for the Response. Period is the Actual Month and period group is just a classification. 

I have Narrowed down the data for us to achieve this, the Logic for forecasting the future month value will be, 

Example logic for 202211(Nov 2022) is(1+ ((Previous year Nov Value (Nov 2021)-(Previous Year Previous month Oct Value(Oct 2021))/(Previous Year Previous month Oct Value(Oct 2021))))*(Current Year Previous month Oct 2022 Value) -----> In the formula Months (Oct,Nov) is taken from the period column and value is taken from the value column.

The Above example is given for calculating the values for Nov 2022, This logic should also work for any future months like (Dec 22,jan 23...) - I need to forecast values like this till 2028 and append those new data rows to the existing data.

Please let me know if you have more doubts, happy to clarify to achieve this.

Scenario	Accounts	Period	Period Group	Client	Value
Actual	Sales	202101	Year 2011	Amazon	101
Actual	Sales	202102	Year 2011	Amazon	114
Actual	Sales	202103	Year 2011	Amazon	126
Actual	Sales	202104	Year 2011	Amazon	138
Actual	Sales	202105	Year 2011	Amazon	150
Actual	Sales	202106	Year 2011	Amazon	162
Actual	Sales	202107	Year 2011	Amazon	174
Actual	Sales	202108	Year 2011	Amazon	186
Actual	Sales	202109	Year 2011	Amazon	198
Actual	Sales	202110	Year 2011	Amazon	210
Actual	Sales	202111	Year 2011	Amazon	222
Actual	Sales	202112	Year 2011	Amazon	234
Actual	Sales	202201	Year 2011	Amazon	246
Actual	Sales	202202	Year 2011	Amazon	259
Actual	Sales	202203	Year 2011	Amazon	272
Actual	Sales	202204	Year 2011	Amazon	285
Actual	Sales	202205	Year 2011	Amazon	298
Actual	Sales	202206	Year 2011	Amazon	311
Actual	Sales	202207	Year 2011	Amazon	324
Actual	Sales	202208	Year 2011	Amazon	337
Actual	Sales	202209	Year 2011	Amazon	350
Actual	Sales	202210	Year 2011	Amazon	363

[AndrewSu]

@kiotsuresh , thanks for the clarification, does that mean for the "Year 2011" classification and forecast for 202211 is

(1+((222-210)/(210)))*363 = 383.74?

If so, why doesn't that value exist in the forecasted values in the dataset?  Note that i filtered the dataset to "Sales", "Forecast" and "Year 2011".  

[kiotsuresh]

Hi @AndrewSu , 

does that mean for the "Year 2011" classification and forecast for 202211 is

(1+((222-210)/(210)))*363 = 383.74? - YES , Correct.

If so, why doesn't that value exist in the forecasted values in the dataset?  Note that i filtered the dataset to "Sales", "Forecast" and "Year 2011". -Only For Sales the forecast data Will be available in the Database, For rest we need to Achieve the Forecasted Values through the formula logic. To make it what i did yesterday is prepared an offline file and prepared the data like Attached, where you will find Actuals,Forecast Values for Sales and Actuals for Revenue and Blank Value Columns for Forecast from 202211 period. we need to populate the Revenue forecast data Using the formula for all the months till 2028.

Please Let me know if you have any more questions. Happy to assist and clarify.

[AndrewSu · Accepted solution]

@kiotsuresh , thanks for the clarification. 

Please see the attached workflow to see one way to get the forecasts.

Note that you can calculate these in bulk by making a Batch Macro to consider all "Accounts" (sales, damage, discount, etc) by using a control parameter(s).

Check out the resources below to learn more about Batch Macros. 

https://community.alteryx.com/t5/Interactive-Lessons/Creating-a-Batch-Macro/ta-p/657923

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

If this helps resolve your issue, please mark this reply as the solution so that others in the community can benefit from our collaboration. 

Thanks!

[kiotsuresh]

@AndrewSu  thank you so much

[kiotsuresh]

@AndrewSu 

Hi Andrew,

How are you doing, Happy new year. 

I have one scenario below,

year group	Measure Name	201301	201302
2011	Sales	1	6
2012	Sales	2	7
2013	Sales	3	8
2014	Sales	4	9
2015	Sales	5	10
2016	Sales	6	11
2017	Sales	7	12
2018	Sales	89	94
2019	Sales	10	15
2020	Sales	11	16
2021	Sales	12	17
2022	Sales	12	17
2023	Sales	14	19
2024	Sales	15	20
2025	Sales	16	21
2026	Sales	17	22
2027	Sales	191	196
2028	Sales	18	23
Total	Average Sales	24.05555556	29.05555556	This Line need to be achieved through alteryx
		Average of row 2 - row 19	Average of row 2 - row 19

Can you help me in achieving this? I have tried multiple logics using the total option( which has only sum) as well as summarize option - didn't worked out any

[AndrewSu]

@kiotsuresh , can you share the workflow with your attempts? 

This should be an easy job for the Summarize tool.