📈 Dynamic Year-over-Year Trend Comparison in CRMA

Have you ever needed to compare year-over-year performance trends — say by month or quarter — without hardcoding the year labels? In this post, I’ll show you how to build a dynamic YoY comparison chart in CRMA using SAQL, with fully auto-updating labels like “2025”, “2024”, and “2023”.

Use Case: Monthly Sales Review Over the Past 3 Years

Imagine you’re a Sales Analyst supporting your Field Sales team. Your stakeholders want to see how many Sales were completed in each month — comparing the past three years (2023, 2024, 2025).

But here’s the twist:

• They want a trend line chart (month on the x-axis, count on the y-axis).
• They want the labels to automatically update next year, i.e., show 2026, 2025, 2024, without you manually editing the dashboard or query.
• And of course, they want it fast and clean.

Why Not Just Group by Year and Month?

If you just use Year-Month and sum sales, your chart will be a long spaghetti line with 30+ data points. It’s messy and hard to compare trends between years.

Instead, we separate the data into three streams, each for one year, and align them by Month (or Week or Quarter). This allows you to create a clean, layered comparison chart, with one line per year.

SAQL Code (Basic Version with Hardcoded Labels)

q = load "Sales";
q_B = filter q by date('Date_Year', 'Date_Month','Date_Day') in ["current year".."current day"];
q_C = filter q by date('Date_Year', 'Date_Month','Date_Day') in ["1 year ago".."1 year ago"];
q_D = filter q by date('Date_Year', 'Date_Month','Date_Day') in ["2 years ago".."2 years ago"];
q = cogroup q_B by 'Date_Month' full, 
             q_C by 'Date_Month' full, 
             q_D by 'Date_Month';
q = foreach q generate coalesce(q_B.'Date_Month', q_C.'Date_Month', q_D.'Date_Month') as 'Month'
  , sum(q_B.'Weekly_Sales') as '2025',  sum(q_C.'Weekly_Sales') as '2024'
  ,  sum(q_D.'Weekly_Sales') as '2023';

Use a Combo chart type

Problem: Hardcoding Year Labels Is Fragile

This works now. But next year? You have to go in and manually update those labels again. That’s not scalable.

What we really need is a dynamic label binding — so the labels always show the correct years based on today’s date.

SAQL Strategy Overview

Here’s the strategy I used:

1. Filter 3 separate datasets by year: current year, last year, two years ago.
2. Cogroup the datasets by time granularity (month, week, or quarter).
3. Use a dynamic binding to fetch the year values from another step (say, a table query).
4. Label each year’s measure dynamically.

The SAQL Code with Dynamic Year Labels

Here’s a simplified version of the SAQL:

q = load "Sales";
q_B = filter q by date('Date_Year', 'Date_Month','Date_Day') in ["current year".."current day"];
q_C = filter q by date('Date_Year', 'Date_Month','Date_Day') in ["1 year ago".."1 year ago"];
q_D = filter q by date('Date_Year', 'Date_Month','Date_Day') in ["2 years ago".."2 years ago"];
q = cogroup q_B by 'Date_Month' full, 
             q_C by 'Date_Month' full, 
             q_D by 'Date_Month';
q = foreach q generate coalesce(q_B.'Date_Month', q_C.'Date_Month', q_D.'Date_Month') as 'Month'
    , sum(q_B.'Weekly_Sales') as '{{cell(Year_Binding_1.result, 2, "Date_Year").asString()}}'
    , sum(q_C.'Weekly_Sales') as '{{cell(Year_Binding_1.result, 1, "Date_Year").asString()}}'
    , sum(q_D.'Weekly_Sales') as '{{cell(Year_Binding_1.result, 0, "Date_Year").asString()}}';

Year_Binding_1: Dynamic Year List

Create a simple query step (e.g., a table) named Year_Binding_1, that lists the past 3 years dynamically. You can definitely just do the compact format. It translate something like this is SAQL:

q = load "your_dataset";
q_A = filter q by date('Date_Year', 'Date_Month', 'Date_Day') 
      in ["current year".."current day"];
q_B = filter q by date('Date_Year', 'Date_Month', 'Date_Day') 
      in ["1 year ago".."1 year ago"];
q_C = filter q by date('Date_Year', 'Date_Month', 'Date_Day') 
      in ["2 years ago".."2 years ago"];
q = cogroup q_B by 'Date_Year' full, q_C by 'Date_Year' full, q_D by 'Date_Year';
q = foreach q generate 
    coalesce(q_A.'Date_Year'， q_B.'Date_Year'，q_C.'Date_Year') as 'Date Year';

This step doesn’t need to be shown in your dashboard — it just powers the binding.

Benefits

• 📅 Auto-updating labels: Future-proof your dashboard.
• ⚙️ No code changes next year: Save maintenance time.
• 🧹 Cleaner chart: No cluttered or hardcoded titles.
• 🧠 Reusable technique: Apply this to quarters, weeks, or other categories.

☀️Enjoy SAQL! ☀️