Edge Cleaning

Edge cleaning is the process of removing leading zeros (before a product's first sale) and trailing zeros (after a product's discontinuation) from time series data. These zero-filled edges distort forecasting models by artificially lowering average demand levels and introducing false seasonality signals.

Function	Description
drop_leading_zeros	Remove initial zero values
drop_trailing_zeros	Remove trailing zero values
drop_edge_zeros	Remove both leading and trailing zeros

Showing 3 of 3

---

anofox_fcst_ts_drop_leading_zeros

Removes initial zero values from the start of each series. Useful for products that had no sales before launch.

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column

Output

Returns table with leading zeros removed from each series.

Example

-- Remove pre-launch zeros
SELECT * FROM anofox_fcst_ts_drop_leading_zeros(
    'sales_by_product',
    product_id,
    date,
    sales
);

warning

Results differ if gap-filling functions were previously applied. Gap-filled NULLs converted to zeros will also be removed.

---

anofox_fcst_ts_drop_trailing_zeros

Removes trailing zero values from the end of each series. Useful for discontinued products.

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column

Output

Returns table with trailing zeros removed from each series.

Example

-- Remove post-discontinuation zeros
SELECT * FROM anofox_fcst_ts_drop_trailing_zeros(
    'sales_by_product',
    product_id,
    date,
    sales
);

warning

Results differ if gap-filling functions were previously applied.

---

anofox_fcst_ts_drop_edge_zeros

Removes both leading and trailing zero values in one operation.

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column

Output

Returns table with edge zeros removed from each series.

Example

-- Clean both edges at once
SELECT * FROM anofox_fcst_ts_drop_edge_zeros(
    'sales_by_product',
    product_id,
    date,
    sales
);

warning

Results differ if gap-filling functions were previously applied.

---

When to Use Edge Cleaning

Scenario	Function	Reason
New product launch	drop_leading_zeros	Remove pre-launch history
Discontinued product	drop_trailing_zeros	Remove post-discontinuation
Product lifecycle	drop_edge_zeros	Clean both ends
Intermittent demand	Don't use	Zeros are valid data points

---

Edge Cleaning Workflow

-- 1. Analyze edge zeros before cleaning
SELECT
    product_id,
    MIN(CASE WHEN sales > 0 THEN date END) as first_sale,
    MAX(CASE WHEN sales > 0 THEN date END) as last_sale,
    MIN(date) as data_start,
    MAX(date) as data_end
FROM sales_by_product
GROUP BY product_id;

-- 2. Clean edges
CREATE TABLE sales_cleaned AS
SELECT * FROM anofox_fcst_ts_drop_edge_zeros(
    'sales_by_product',
    product_id,
    date,
    sales
);

-- 3. Verify cleaning
SELECT
    product_id,
    MIN(date) as new_start,
    MAX(date) as new_end,
    COUNT(*) as remaining_rows
FROM sales_cleaned
GROUP BY product_id;

---

Order of Operations

The recommended order for data preparation:

-- 1. Edge cleaning FIRST (before gap filling)
CREATE TABLE step1 AS
SELECT * FROM anofox_fcst_ts_drop_edge_zeros(
    'raw_data', product_id, date, sales
);

-- 2. Gap filling SECOND
CREATE TABLE step2 AS
SELECT * FROM anofox_fcst_ts_fill_gaps(
    'step1', product_id, date, sales, '1d'
);

-- 3. Imputation THIRD
CREATE TABLE step3 AS
SELECT * FROM anofox_fcst_ts_fill_nulls_forward(
    'step2', product_id, date, sales
);

-- 4. Filtering LAST
CREATE TABLE ready AS
SELECT * FROM anofox_fcst_ts_drop_short(
    'step3', product_id, 30
);

---

Frequently Asked Questions

Should I clean edges before or after gap filling?

Always clean edges before gap filling. If you gap-fill first, the gap-filling process may convert NULLs to zeros at the edges, which then get removed by edge cleaning -- producing unexpected results. The recommended order is: (1) edge cleaning, (2) gap filling, (3) imputation, (4) filtering. See the Order of Operations section above.

Will edge cleaning remove legitimate zero-demand periods in the middle of my series?

No. Edge cleaning only removes zeros at the start (leading) and end (trailing) of each series. Zeros in the middle of the series are never touched. For data with genuinely intermittent demand throughout, use intermittent demand models instead of removing internal zeros.

How do I know if my data has edge zero problems?

Compare each series' first non-zero observation date with the data start date and the last non-zero observation date with the data end date. If there is a large gap at either end, edge cleaning will help. The Edge Cleaning Workflow section above shows a diagnostic query that identifies these gaps per product.