Edge Cleaning

Remove leading and trailing zeros from time series edges.

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
);

---