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 |
Solved! Go to Solution.
@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.
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 |
@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".
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.
@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
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!
@AndrewSu thank you so much
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
@kiotsuresh , can you share the workflow with your attempts?
This should be an easy job for the Summarize tool.