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) |
@kiotsuresh you can accomplish this using a Multi-Row Formula tool. I have attached an example.
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.
@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.
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.
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 |
@MatthewO the values present in the table snippet are the sample ones i have pasted for reference.
@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.
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.