¶ Description
Computes delta between rows.
¶ Parameters
¶ Parameters tab
Parameters:
- Customer id
- Compute delta's for these KPI's
¶ Description tab
Parameters:
- Script name
- Short description
- Revision
- Description
¶ Configuration tab
See dedicated page for more information.
¶ About
deltaKPI computes, within each group of records, the difference (delta) of one or more KPI columns between the current row and the previous row in time.
Data must be sorted by id and by date (ascending) before reaching this box. The first row of each id group has no predecessor; its deltas are NULL.
¶ Prerequisites
-
Input table includes:
id– grouping key (string/int)date– ordering key (datetime or ISO-8601 text)- One or more numeric KPI columns (e.g.,
kpi_revenue,kpi_visits,kpi_csat)
-
Upstream Sort step guarantees
ORDER BY id ASC, date ASC. -
No duplicates of (
id,date) inside a group. -
KPI columns are strictly numeric (no thousand separators / mixed types).
¶ Input Requirements
| Column | Type | Required | Notes |
|---|---|---|---|
id |
string/int | ✔ | Partition key for delta computation. |
date |
datetime or ISO-8601 text | ✔ | Sort key within id. Must be non-decreasing. |
KPI columns (e.g., kpi_revenue, kpi_visits, kpi_csat) |
numeric | ✔ | Deltas are computed for each selected KPI. |
Delta definition (per KPI k within an id ordered by date):
¶ Parameters
|
Inter-parameter rule: idxId defines the partition; deltas are computed per selected KPI inside each partition after sorting by date.
¶ Output
For every selected KPI column, deltaKPI appends a new column:
| Input KPI | Output column (pattern) | Type | First row per id |
|---|---|---|---|
kpi_revenue |
delta_kpi_revenue |
numeric | NULL |
kpi_visits |
delta_kpi_visits |
numeric | NULL |
kpi_csat |
delta_kpi_csat |
numeric | NULL |
Naming follows:
delta_+ original KPI column name.
¶ Example Workflow (step-by-step)
-
Read your CSV/Parquet with UTF-8, delimiter
,, header ON. -
Sort upstream:
- Column 1:
id→ ascending (alphabetical/numeric) - Column 2:
date→ ascending (date/time or ISO text)
- Column 1:
-
Configure deltaKPI:
- Customer id (
idxId) =id - KPI list (
idxKPI) =kpi_revenue, kpi_visits, kpi_csat
- Customer id (
-
Run the box.
-
Validate:
- Row count unchanged.
- New columns exist:
delta_kpi_revenue,delta_kpi_visits,delta_kpi_csat. - For each
id, first row deltas areNULL; subsequent rows equal current − previous.
¶ Troubleshooting
| Symptom / Log | Likely Cause | Precise Fix |
|---|---|---|
| “Input table must be sorted on (1) id and (2) date columns.” | Data not sorted; wrong column names used | Add a Sort before deltaKPI with id asc, date asc. Ensure columns are named exactly id, date (or rename upstream). |
Delta columns all NULL |
KPI columns not selected or are non-numeric | Set idxKPI to all KPI columns; cast to numeric; remove thousand separators. |
| Unexpected delta values | Duplicates or out-of-order date within an id |
De-duplicate (id,date); enforce strict ascending order. |
| Many decimals (e.g., 0.0999999) | Floating-point representation | Round downstream (e.g., 2–4 decimals) for display. |
¶ Use Cases
- Day-over-day/period-over-period growth per account/customer.
- Operational swings detection (spikes/drops in visits or revenue).
- Health monitoring with simple momentum features for modeling.
- Inventory or sales movement between consecutive transactions.
