Comparing Year-To-Date Financial Data Across Years

It is the middle of March and leadership wants to know how this year compares to last year. Full-year totals are no help in March, and neither is stacking ten weeks of 2026 against all of 2025. What they actually want is a like-for-like slice: January and February 2026 against January and February 2025, broken down by product category. Which categories are growing? Which are declining? By how much?

This is a year-over-year (YoY) comparison, and it normally requires a SQL query with self-joins or a BI tool with calculated fields. In ExploreMyData, you can build it with a multi-view pipeline. Here is how.

What you need

An orders or transactions file spanning at least two calendar years. You need columns for order_date, category (or product, region, etc.), and revenue (or whatever metric you are comparing).

Step 1: Extract year and month

Click the green + in the Pipeline panel and select Extract Date Part from the Date group. Apply it twice:

1. Extract year from order_date → name it order_year
2. Extract month from order_date → name it order_month

Now every row has a year number and a month number. This is what makes the comparison possible. You can match January 2026 to January 2025 by joining on the month number.

Step 2: Filter to the comparison months

Select Filter from the Filter & Sort group:

1. Column: order_month
2. Operator: less than or equal to
3. Value: 2

That keeps January and February on both sides and drops everything else, including the half-finished March that is still filling up. Two complete months against two complete months is a fair fight. The cutoff is the one number you will change over the year: on 1 April it becomes 3, in July it becomes 6, and every step after it recalculates on its own.

Step 3: Create two views

This is where ExploreMyData's multi-view feature comes in. From the filtered dataset, create two separate views:

View A (this year): Add a Filter step where order_year = 2026. Then click the green + and select Group & Aggregate from the Aggregate group: group by category and order_month, then add one aggregation with function SUM and column revenue.

There is no output-name field on that panel. The alias is always <column>_<function>, so what you get back is revenue_sum. Add a Rename Columns step from the Columns group and rename it to revenue_2026.

View B (last year): The same three steps, but filter where order_year = 2025 and rename revenue_sum to revenue_2025.

The rename is not decoration. Push two columns both called revenue_sum into a join and the second one arrives as revenue_sum_1, and you will spend the rest of the pipeline guessing which year that is. Each view now holds one row per category per month with the total revenue for that year.

Step 4: Build a single join key on each view

Here is the part people trip over. The match you want is category and month, but the Join panel takes exactly one key pair: one "Left key", one "Right key", and no button to add a second row. So you build the composite key yourself, before the join, on both sides.

On View A, click the green + in the Pipeline panel and select Combine Columns from the Columns group. The whole panel is one chip input, so drop three chips into it in order:

1. The category column chip
2. A literal text chip containing | (type the character, press enter, it becomes a chip of its own)
3. The order_month column chip

Set "Apply results into" to New Column and name it join_key. Then repeat the identical step on View B: same three chips, same order, same column name. January software now reads Software|1 on both sides.

The SQL it generates is a plain concatenation: CONCAT(COALESCE(CAST("category" AS VARCHAR), ''), '|', COALESCE(CAST("order_month" AS VARCHAR), ''))

Do not skip the separator chip. Without it, category "Hardware" in month 12 and a category called "Hardware1" in month 2 both flatten to Hardware12 and cheerfully join to each other. Pick a character your category names cannot contain.

Step 5: Join the two years together

Select Join from the Data group. The panel walks you through three numbered sections:

1. Select table & join type: pick View B (2025) as the table and Left as the type. Inner works too, but Left is what keeps a category that sold this year and did not exist last year, and those rows are usually the first thing anyone asks about.
2. Match columns: Left key join_key, right key join_key. One pair, which is all you need now.
3. Columns to include from right table: select revenue_2025 and nothing else. Leave this empty and you get every right-hand column back, including second copies of category, order_month and join_key, which the grid renames with a _1 suffix.

The result is one row per category per month with revenue_2026 and revenue_2025 side by side. join_key has done its job and just rides along; drop it with a Select Columns step at the end if you want a tidy export.

Step 6: Calculate the year-over-year change

Select Math from the Transform group. It is a single free-text formula box, with column names in double quotes:

("revenue_2026" - "revenue_2025") / "revenue_2025" * 100

Set "Apply results into" to New Column and call it yoy_change_pct. A value of 15 means 15% growth. A value of -8 means an 8% decline.

Guard the denominator before you ship this

That formula divides by last year. Two kinds of row make that go wrong, and both of them are ordinary business reality.

• Last year is missing. The Left join leaves revenue_2025 NULL for any category that did not exist in 2025. NULL propagates through arithmetic, so yoy_change_pct comes out blank.
• Last year is zero. A category that billed nothing last January gives you a division by zero. DuckDB does not raise an error for this: it returns NULL, same as the missing case. No red banner, no failed step, just a quiet blank where a headline growth number should be. That is the nastier of the two, because a genuine 100% jump and an unusable one look identical in the grid.

The fix lives on the Math step itself. Open its condition and enter "revenue_2025" > 0. The step then generates:

CASE WHEN ("revenue_2025" > 0) THEN ("revenue_2026" - "revenue_2025") / "revenue_2025" * 100 ELSE NULL END

Rows with no usable base now come back blank on purpose rather than blank by accident, and the next step gives them a label of their own instead of leaving them to be read as zero growth. (If you would rather keep it in one place, the same CASE expression pasted straight into the formula box works too.)

For the absolute change, add a second Math column named yoy_change_abs with "revenue_2026" - COALESCE("revenue_2025", 0). Subtraction has no denominator to blow up, so treating a missing year as zero is safe and gives new categories their full amount as the gain.

Step 7: Label the growth direction

Select Add Column from the Columns group, name it trend, and give it a conditional expression:

• If revenue_2025 is NULL or 0 → "New"
• If yoy_change_pct > 2 → "Growing"
• If yoy_change_pct < -2 → "Declining"
• Otherwise → "Flat"

CASE WHEN "revenue_2025" IS NULL OR "revenue_2025" = 0 THEN 'New' WHEN "yoy_change_pct" > 2 THEN 'Growing' WHEN "yoy_change_pct" < -2 THEN 'Declining' ELSE 'Flat' END

Keep the "New" test first. Leave it out and every new category lands in "Flat", because a comparison against NULL is not true and falls through to the ELSE. The 2% threshold keeps small wobbles from being read as trends; move it to whatever counts as noise in your business.

Reading the results

Click the yoy_change_pct header to sort the grid: descending puts the fastest-growing categories on top, ascending puts the steepest declines there. That is a view-level sort, so it does not add a pipeline step. When you want the problem list on its own, add a Filter for trend = 'Declining'.

For a category-level summary, add one more Group & Aggregate at the end: group by category with SUM on revenue_2026 and SUM on revenue_2025. They come back as revenue_2026_sum and revenue_2025_sum, so repeat the Math step against those two names, condition included. That collapses the monthly rows into one line per category covering the whole year to date, which in mid-March means January plus February.

The full pipeline

1. Extract Date Part - year and month from order_date
2. Filter - order_month less than or equal to 2
3. Two views - one filtered to 2026, one to 2025, each Group & Aggregate by category + month with SUM(revenue), each renaming revenue_sum to revenue_2026 or revenue_2025
4. Combine Columns - category, a "|" chip and order_month into join_key, on both views
5. Join - Left, join_key = join_key, pulling only revenue_2025 from the right
6. Math - percentage change, with the condition "revenue_2025" > 0
7. Add Column - trend label, New test first

When March closes, change one number in the Filter step and every total, percentage and label below it recalculates. Point the same chain at a different file with different category names and it still runs, because not one of these steps knows what your categories are called.

Compare your year-over-year data →