The stock-out smoothing and promotional smoothing are standard part of the 6.2 version of AGR.
In out-of-stock smoothing, the sales history is adjusted with the average sales during the periods where there are no sales because of stock-outs.
Several consecutive days are called stock-out period if all of the following applies:
The procedures [inv].[histories_bridge_stockouts] and [inv].[histories_bridge_stockouts_one_location] are used in stock out smoothing calculations.
Following configurations can be applied to stock-out smoothing:
Found in core.setting (on stg):
In procedure [inv].[histories_bridge_stockouts_one_location] where the average sale is calculated, you can add/remove tables and change the COALESCE as needed. Also, you can add/remove tables where the “find first sale” is executed.
If you like to run out of stock smoothing on another level than location level, e.g. item level, you can follow these instructions here: https://stackoverflow.com/c/agrdynamics/questions/1181
Add the data serie (bridged_sale in this example) to the data_element_series table.
INSERT INTO data_element_series(
[id], [name], [group_id], [system_default_element], [client_config]
,[server_config])
VALUES(
100, 'bridged_sale', 2, 1, NULL,
'{
"caption":"Bridged Sale",
"description":"Bridged Sale",
"data_object":{
"name":"dbo.histories_bridged_sale",
"data_element_ref_table_id":1,
"id_column":"item_id",
"date_column":"history_date",
"value_column":"value"
}
}'
)
INSERT [dbo].[chart_elements] ([id], [name], [chart_label], [description], [data_element_id], [starts_stops_today], [aggregation_calc_func], [aggregated_over_period], [hidden_in_charts], [editable], [editable_element_id], [highcharts_series_options])
VALUES (100, N'bridged_sale', N'Bridged sale', NULL, 100, N'stops', N'sum', 1, 0, 0, NULL, N'{"id":"Bridged sale","name":"SALE_BRIDGED","data":[],"color":"#089e06","type":"column","index":5,"legendIndex":4,"zIndex":6,"stacking":"normal","stack":3}')
GO
ExampleIf you have customized sale, you should not run the following query blindly. Compare the ‘calculation’ value to the standard and make appropriate adjustments to your setup.
-- Be careful when updating
--UPDATE dbo.data_element_series
SET server_config = JSON_MODIFY(server_config,'$.generated_view.value_column_calculated.calculation','COALESCE([adjusted_sale],[connected_sale],[bridged_sale],[bridged_promo_sale],[original_sale],0)')
WHERE name = 'sale'
The average sale is calculated from the last positive sale found before stockout period.
Otherwise, if no sale is found within the days_back_to_search_for_sale period, the average is simply calculated from the first stockout day.
E.g. if the bridged period starts on 31.08 we go [30] days back from 30.08 to calculate average sale.
The average sale for stock out period is calculated with the following formula:
(The variables inside the COALESCE can be changed/removed as needed.)
Notice here that the code smooths on older OOS values if they are in place.
E.g. if there are two OOS periods in the last 30 days, then when calculating the OOS value for the later one it will include earlier stock out OOS values in the bridging.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
--
-- rep.histories_bridge_stockouts_one_location
--
IF OBJECT_ID('rep.histories_bridge_stockouts_one_location') IS NOT NULL
DROP PROCEDURE [rep].[histories_bridge_stockouts_one_location]
GO
-- ===============================================================================
-- Author: Jorundur Matthiasson
-- Create date: 02.06.2020
-- Description: Bridging stockouts for sale for location
--
-- 25.08.2020.RJ Created
-- 16.09.2020.JM DEV-1586 Bridge stockout with new logic
-- 08.03.2021.JM DEV-2153 Improve to handle sales inside stockout period
-- ===============================================================================
CREATE PROCEDURE [rep].[histories_bridge_stockouts_one_location]
@lId INT = -1,
@location_id INT = NULL,
@days_back INT = 30, -- Days back to search for stockout (for first run you need a high number to reach first day of sale)
@days_back_to_calc_avg INT = 30, -- Days back to calculate average
@days_back_to_search_for_sale INT = 60, -- Days back to search for sale, if set average is calculated from first positive sale found, if zero average is simply calclulated from first stockout day
@stockout_qty INT = 0, -- Defined stockout quantity, usually zero but sometimes a higher number is required
@stop_bridging_after_days INT = 30 -- Max length of stockout period
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
BEGIN TRY
EXEC log.log_transfer @log_id = @lId, @proc_id = @@PROCID, @extra_info = @location_id
DECLARE @yesterday DATE = DATEADD(DAY, -1, GETDATE())
DECLARE @from_date DATE = DATEADD(DAY, -@days_back, @yesterday)
DECLARE @avg_from_date DATE, @avg_to_date DATE, @avg_sale DECIMAL(18, 4)
DECLARE @bridged_from_date DATE, @bridged_to_date DATE, @bridged_max_date DATE
DECLARE @search_for_sale_from_date DATE, @search_for_sale_to_date DATE, @first_sale_date DATE
DECLARE @description NVARCHAR(255) = 'w.Bridged sale, '+ CONVERT(NVARCHAR, @days_back_to_calc_avg) + ' days'
DECLARE @item_id INT, @stockout_from_date DATE, @stockout_to_date DATE
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR LOCAL FAST_FORWARD
FOR WITH cte AS
(
-- All stock rows with lag info
SELECT hs.item_id, hs.history_date, stock_value = hs.[value], stock_move_value = ISNULL(hsm.stock_move, 0),
lag_history_date = LAG(hs.history_date) OVER (PARTITION BY hs.item_id ORDER BY hs.history_date),
lag_stock_value = LAG(hs.[value]) OVER (PARTITION BY hs.item_id ORDER BY hs.history_date)
FROM rep.items i
INNER JOIN rep.histories_stock hs ON hs.item_id = i.id AND hs.history_date >= @from_date
LEFT JOIN rep.histories_sales_and_stock_move hsm ON hsm.item_id = hs.item_id AND hsm.history_date = hs.history_date
WHERE i.location_id = @location_id
),
cte2 AS
(
-- Bit columns stock, stock_before and stock_move
SELECT item_id, history_date, stock_value, stock_move_value, lag_history_date, lag_stock_value,
stock = CASE WHEN stock_value <= @stockout_qty THEN 0 WHEN stock_value > @stockout_qty THEN 1 END,
stock_before = CASE WHEN lag_stock_value <= @stockout_qty THEN 0 WHEN ISNULL(lag_stock_value, 1) > @stockout_qty THEN 1 END, -- ISNULL for sale that goes directly to stockout with no sale history (new product)
stock_move = CASE WHEN stock_move_value < 0 THEN 1 ELSE 0 END
FROM cte
),
cte3 AS
(
-- Adding lead info to get start/end
SELECT item_id, history_date, stock, stock_before, stock_move,
lead_history_date = LEAD(history_date) OVER (PARTITION BY item_id ORDER BY history_date),
lead_stock = LEAD(stock) OVER (PARTITION BY item_id ORDER BY history_date),
lead_stock_move = LEAD(stock_move) OVER (PARTITION BY item_id ORDER BY history_date)
FROM cte2
WHERE (stock = 0 AND stock_before = 1) OR (stock = 1 AND stock_before = 0) OR (stock = 0 AND stock_before = 0 AND stock_move = 1)
)
SELECT item_id,
stockout_from_date = CASE WHEN stock = 0 AND stock_before = 1 AND stock_move = 0 THEN history_date -- Normal start (no sale)
WHEN stock = 0 AND stock_before = 1 AND stock_move = 1 THEN DATEADD(DAY, 1, history_date) -- Normal start (sale, so add one day)
WHEN stock = 0 AND stock_before = 0 AND stock_move = 1 THEN DATEADD(DAY, 1, history_date) -- Sale in stockout start
END,
stockout_to_date = CASE WHEN stock = 0 AND stock_before = 1 AND lead_stock = 1 THEN DATEADD(DAY, -1, lead_history_date) -- Normal start :: Normal end
WHEN stock = 0 AND stock_before = 1 AND lead_stock = 0 AND lead_stock_move = 1 THEN DATEADD(DAY, -1, lead_history_date) -- Normal start :: Sale in stockout end
WHEN stock = 0 AND stock_before = 1 AND lead_history_date IS NULL THEN @yesterday -- Normal start :: Still going
WHEN stock = 0 AND stock_before = 0 AND stock_move = 1 AND lead_stock = 1 THEN DATEADD(DAY, -1, lead_history_date) -- Sale in stockout start :: Normal end
WHEN stock = 0 AND stock_before = 0 AND stock_move = 1 AND lead_stock = 0 AND lead_stock_move = 1 THEN DATEADD(DAY, -1, lead_history_date) -- Sale in stockout start :: Sale in stockout end
WHEN stock = 0 AND stock_before = 0 AND stock_move = 1 AND lead_history_date IS NULL THEN @yesterday -- Sale in stockout start :: Still going
END
FROM cte3
WHERE NOT(stock = 1 AND stock_before = 0)
ORDER BY 1, 2
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @item_id, @stockout_from_date, @stockout_to_date
WHILE @@FETCH_STATUS = 0
BEGIN
-- Set bridged period
SET @bridged_from_date = @stockout_from_date
SET @bridged_to_date = @stockout_to_date
IF @bridged_to_date > @yesterday
SET @bridged_to_date = @yesterday
IF @bridged_to_date >= @bridged_from_date
BEGIN
-- Cap on @stop_bridging_after_days if needed
SET @bridged_max_date = DATEADD(DAY, @stop_bridging_after_days - 1, @bridged_from_date)
IF @bridged_to_date > @bridged_max_date
SET @bridged_to_date = @bridged_max_date
-- Initialize average sale
SELECT @avg_from_date = NULL, @avg_to_date = NULL, @avg_sale = NULL
-- Set average period
IF @days_back_to_search_for_sale > 0
BEGIN
SET @search_for_sale_to_date = @stockout_from_date
SET @search_for_sale_from_date = DATEADD(DAY, -(@days_back_to_search_for_sale - 1), @search_for_sale_to_date)
-- Find first sale
-- *** *** *** ADD/REMOVE TABLES IF NEEDED *** *** ***
;WITH cte AS
(
-- original_sale
SELECT TOP 1 history_date
FROM rep.histories_sale
WHERE item_id = @item_id AND history_date BETWEEN @search_for_sale_from_date AND @search_for_sale_to_date AND [value] > 0
ORDER BY history_date DESC
-- adjusted_sale
UNION ALL
SELECT TOP 1 history_date
FROM rep.histories_adjusted_sale
WHERE item_id = @item_id AND history_date BETWEEN @search_for_sale_from_date AND @search_for_sale_to_date AND [value] > 0
ORDER BY history_date DESC
-- connected_sale
UNION ALL
SELECT TOP 1 history_date
FROM rep.histories_connected_sale
WHERE item_id = @item_id AND history_date BETWEEN @search_for_sale_from_date AND @search_for_sale_to_date AND [value] > 0
ORDER BY history_date DESC
-- stockout_smoothing
UNION ALL
SELECT TOP 1 history_date
FROM rep.histories_bridged_sale
WHERE item_id = @item_id AND history_date BETWEEN @search_for_sale_from_date AND @search_for_sale_to_date AND [value] > 0
ORDER BY history_date DESC
-- promo_smoothing
UNION ALL
SELECT TOP 1 history_date
FROM rep.histories_smoothing_sale
WHERE item_id = @item_id AND history_date BETWEEN @search_for_sale_from_date AND @search_for_sale_to_date AND [value] > 0
ORDER BY history_date DESC
)
SELECT @first_sale_date = MAX(history_date) FROM cte
IF @first_sale_date IS NOT NULL
BEGIN
SET @avg_to_date = @first_sale_date
SET @avg_from_date = DATEADD(DAY, -(@days_back_to_calc_avg - 1), @avg_to_date)
END
END
ELSE
BEGIN
SET @avg_to_date = @stockout_from_date
SET @avg_from_date = DATEADD(DAY, -(@days_back_to_calc_avg - 1), @avg_to_date)
END
-- Calculate average sale for x days before stockout period
-- *** *** *** ADD/REMOVE TABLES AND CHANGE COALESCE IF NEEDED *** *** ***
IF @avg_to_date IS NOT NULL
BEGIN
;WITH cte AS
(
-- original_sale
SELECT history_date, original_sale = [value], adjusted_sale = NULL, connected_sale = NULL, stockout_smoothing = NULL, promo_smoothing = NULL
FROM rep.histories_sale
WHERE item_id = @item_id AND history_date BETWEEN @avg_from_date AND @avg_to_date
-- adjusted_sale
UNION ALL
SELECT history_date, original_sale = NULL, adjusted_sale = [value], connected_sale = NULL, stockout_smoothing = NULL, promo_smoothing = NULL
FROM rep.histories_adjusted_sale
WHERE item_id = @item_id AND history_date BETWEEN @avg_from_date AND @avg_to_date
-- connected_sale
UNION ALL
SELECT history_date, original_sale = NULL, adjusted_sale = NULL, connected_sale = [value], stockout_smoothing = NULL, promo_smoothing = NULL
FROM rep.histories_connected_sale
WHERE item_id = @item_id AND history_date BETWEEN @avg_from_date AND @avg_to_date
-- stockout_smoothing
UNION ALL
SELECT history_date, original_sale = NULL, adjusted_sale = NULL, connected_sale = NULL, stockout_smoothing = [value], promo_smoothing = NULL
FROM rep.histories_bridged_sale
WHERE item_id = @item_id AND history_date BETWEEN @avg_from_date AND @avg_to_date
-- promo_smoothing
UNION ALL
SELECT history_date, original_sale = NULL, adjusted_sale = NULL, connected_sale = NULL, stockout_smoothing = NULL, promo_smoothing = [value]
FROM rep.histories_smoothing_sale
WHERE item_id = @item_id AND history_date BETWEEN @avg_from_date AND @avg_to_date
),
cte2 AS
(
SELECT history_date, original_sale = SUM(original_sale), adjusted_sale = SUM(adjusted_sale), connected_sale = SUM(connected_sale),
stockout_smoothing = SUM(stockout_smoothing), promo_smoothing = SUM(promo_smoothing)
FROM cte
GROUP BY history_date
)
SELECT @avg_sale = ROUND(SUM(COALESCE(adjusted_sale, connected_sale, promo_smoothing, stockout_smoothing, original_sale, 0)) / @days_back_to_calc_avg, 2) FROM cte2
END
IF @avg_sale > 0
BEGIN
-- Merge into rep.histories_bridged_sale
;WITH TARGET AS
(
SELECT item_id, history_date, [value], [origin], [description], created_at, updated_at
FROM rep.histories_bridged_sale
WHERE item_id = @item_id AND history_date BETWEEN @bridged_from_date AND @bridged_to_date
)
MERGE INTO TARGET USING
(
-- Joining on sale table to prevent bridging over sale in stockout period (f.e. if sale=10, stock_move=10-10=0 and stock = 0)
SELECT item_id = @item_id, history_date = d.[date]
FROM date_table d
LEFT JOIN rep.histories_sale s ON s.item_id = @item_id AND s.history_date = d.[date]
WHERE d.[date] BETWEEN @bridged_from_date AND @bridged_to_date AND (s.item_id IS NULL OR s.[value] <= 0)
)
AS SOURCE ON
(
SOURCE.item_id = TARGET.item_id AND SOURCE.history_date = TARGET.history_date
)
WHEN MATCHED AND TARGET.[value] != @avg_sale THEN
UPDATE SET TARGET.[value] = @avg_sale, TARGET.updated_at = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (item_id, history_date, [value], origin, [description], updated_at, created_at)
VALUES (SOURCE.item_id, SOURCE.history_date, @avg_sale, '0', @description, GETDATE(), GETDATE());
END
END
FETCH NEXT FROM @Cursor INTO @item_id, @stockout_from_date, @stockout_to_date
END
CLOSE @Cursor
DEALLOCATE @Cursor
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
EXEC log.log_error @lId
END CATCH
EXEC log.log_transfer_step_duration @log_id = @lId, @proc_id = @@PROCID
END
GO IF OBJECT_ID('rep.histories_bridge_stockouts') IS NOT NULL
DROP PROCEDURE [rep].[histories_bridge_stockouts]
GO
-- ================================================================================
-- Author: Jorundur Matthiasson
-- Create date: 16.09.2020
-- Description: Bridging stockouts for sale
-- Cursor calling rep.histories_bridge_stockouts_one_location for each location_id
--
-- 16.09.2020.JM Created
-- ================================================================================
CREATE PROCEDURE [rep].[histories_bridge_stockouts]
@lId INT = -99
AS
BEGIN
SET NOCOUNT ON
PRINT '#'
PRINT '# rep.histories_bridge_stockouts'
PRINT '#'
PRINT ''
EXEC @lId = log.log_transfer @log_id = @lId, @proc_id = @@PROCID
DECLARE @location_id INT
-- *** *** *** CHANGE CURSOR SELECT FOR CUSTOMIZED LOCATIONS *** *** ***
DECLARE @cursor CURSOR
SET @cursor = CURSOR LOCAL FAST_FORWARD
FOR SELECT id FROM rep.locations WHERE location_type IN ('warehouse') ORDER BY id
OPEN @cursor
FETCH NEXT FROM @cursor INTO @location_id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(VARCHAR, SYSDATETIME(), 120) + ' : location_id = ' + CONVERT(VARCHAR, @location_id)
RAISERROR ('', 0, 1) WITH NOWAIT
EXEC rep.histories_bridge_stockouts_one_location
@lId,
@location_id,
@days_back = 30, -- Days back to search for stockout (for first run you need a high number to reach first day of sale)
@days_back_to_calc_avg = 30, -- Days back to calculate average
@days_back_to_search_for_sale = 60, -- Days back to search for sale, if set average is calculated from first positive sale found, if zero average is simply calclulated from first stockout day
@stockout_qty = 0, -- Defined stockout quantity, usually zero but sometimes a higher number is required
@stop_bridging_after_days = 30 -- Max length of stockout period
FETCH NEXT FROM @cursor INTO @location_id
END
CLOSE @cursor
DEALLOCATE @cursor
EXEC log.log_transfer_step_duration @log_id = @lId, @proc_id = @@PROCID
END
GOFor promotion periods, the sales history is replaced with average sales before promotions. That is called promotional smoothing and is used to reduce the effect of promotions on the forecast.
The procedure with the promotional smoothing calculations is [inv].[histories_smoothing].
Following configurations can be applied to promo smoothing:
Found in core.setting (on stg):
You can customize the view [inv].[v_histories_smoothing_source] to point to the table where to take the promotion days from. Default is the [inv].[planner_promo] table.
The following table contains sale data for November 2019 and two weeks in December, but in December there was promotion (but only on weekdays, that’s why there is regular sale on 08.12.19).
The promotional smoothing for promotions period 02.12-06.12.2019 is calculated as the average sale from last 30 days. That gives 648.92.
Then for promotions period 09.12-13.12.2019, the last sale was on 08.12.19. Since 5 days within the 30 day period are promotions and values from promotions are not taken into account, there are fewer days here that are included in the average.
Therefore the average sale value for period 09.12-13.12.2019 is 628.33.
history_date sale_value adjusted_sale previous_sale_date marked_as_bridged
11/1/2019 681 681 - 0
11/3/2019 387 387 - 0
11/4/2019 299 299 - 0
11/5/2019 365 365 - 0
11/6/2019 579 579 - 0
11/7/2019 1647 1647 - 0
11/8/2019 1001 1001 - 0
11/10/2019 283 283 - 0
11/11/2019 429 429 - 0
11/12/2019 236 236 - 0
11/13/2019 739 739 - 0
11/14/2019 1259 1259 - 0
11/15/2019 629 629 - 0
11/17/2019 286 286 - 0
11/18/2019 256 256 - 0
11/19/2019 375 375 - 0
11/20/2019 398 398 - 0
11/21/2019 1749 1749 - 0
11/22/2019 802 802 - 0
11/24/2019 219 219 - 0
11/25/2019 363 363 - 0
11/26/2019 394 394 - 0
11/27/2019 560 560 - 0
11/28/2019 1541 1541 - 0
11/29/2019 1050 1050 - 0
12/1/2019 345 345 - 0
12/2/2019 0 648.92 12/1/2019 1
12/3/2019 0 648.92 12/1/2019 1
12/4/2019 0 648.92 12/1/2019 1
12/5/2019 0 648.92 12/1/2019 1
12/6/2019 0 648.92 12/1/2019 1
12/8/2019 281 281 - 0
12/9/2019 0 628.33 12/8/2019 1
12/10/2019 0 628.33 12/8/2019 1
12/11/2019 0 628.33 12/8/2019 1
12/12/2019 0 628.33 12/8/2019 1
12/13/2019 0 628.33 12/8/2019 1