A Somewhat More Involved Date Comparison

An example page for https://www.evidence.studio/

Introduction

Many Components now have a "compare to" option, and thats going to serve most purposes, but what if you want to plot a metric over time against a comparison period?

This is definitely more involved, but it solves that case, and allows for a little less repetition in component settings. This is probably not the only way to do it, just an example for anyone that might find it useful

This example only uses the demo_daily_orders source, so can be tried out by anyone

Setup

Lets start with a date picker, a comparison selector, and for good measure a filter for other attributes in the data {% range_calendar id="date_range" default_range="last 3 months" /%}

{% comparison_selector id="compare_to" preset_values=["prior period","prior year"] default_value="prior period" /%}

{% table_filter id="orders_filter" data="demo_daily_orders" columns=["category","avg_transaction_value"] /%}

Initial Selection

We start by filtering our data based on the above, nothing complex yet, just applying the filters and aggregating

SELECT
  date,
  category,
  sum(transactions) as transactions
FROM demo_daily_orders
WHERE date {{ date_range.between | = date}} --fallback value to cover 'All Time' selection
  AND {{orders_filter.filter}}
GROUP BY ALL

{% line_chart data="orders_filtered" x="date" y="sum(transactions) as transactions" /%}

Comparison logic and selection

Now we can get on with the complexity! Depending on the comparison choice, we need to get start and end dates for the comparison period. Essentially this is a repeated case statement to apply the right subtraction to the selected start and end dates

SELECT
  {{ date_range.start | toDate( '2021-10-01' ) }} AS start_date, --the fallback values could be anything, even pulled in based on a data source
  {{ date_range.end   | toDate( '2025-09-30' ) }} AS end_date,
  (end_date - start_date) + 1 AS range_days,
  CASE WHEN '{{compare_to.literal}}' = 'prior period' THEN start_date - range_days
       WHEN '{{compare_to.literal}}' = 'prior year'   THEN start_date - 365
  ELSE NULL
  END AS prev_start_date,
  CASE WHEN '{{compare_to.literal}}' = 'prior period' THEN end_date - range_days
       WHEN '{{compare_to.literal}}' = 'prior year'   THEN end_date - 365
  ELSE NULL
  END AS prev_end_date,
  CASE WHEN '{{compare_to.literal}}' = 'prior period' THEN range_days
       WHEN '{{compare_to.literal}}' = 'prior year'   THEN 365
  ELSE NULL
  END AS comparison_modifier

Selected Date Range: {% value data="comparison_dates" value="min(start_date)" /%} --> {% value data="comparison_dates" value="min(end_date)" /%}
Comparison Date Range: {% value data="comparison_dates" value="min(prev_start_date)" /%} --> {% value data="comparison_dates" value="min(prev_end_date)" /%}
Size of Date Range: {% value data="comparison_dates" value="min(range_days)" fmt="num" /%} days

With the prior date range sorted, we can apply it to the source data, not forgetting to include the additional filter so we'll be comparing apples to apples The easiest way i've found to apply this is cross joining our dates to the source data, knowing that there will only be one row to join to, so no unintended duplication can happen

SELECT
  date,
  date + c.comparison_modifier AS comparison_date, --this will be important later
  category,
  sum(transactions) as transactions
FROM demo_daily_orders 
CROSS JOIN {{comparison_dates}} c
WHERE date BETWEEN c.prev_start_date AND c.prev_end_date
  AND {{orders_filter.filter}}
GROUP BY ALL

{% line_chart data="orders_comparison" x="date" y="sum(transactions) as previous_transactions" /%}

Combining selection and comparison

Now for the fun part, we can combine the 2 datasets together Joining means we need matching keys, even on date, which is why the comparison data has not just the date of its activity, but a corresponding date that tells us what day in the future to match it to

SELECT
  COALESCE(current.date, previous.comparison_date) AS date,
  current.date,
  previous.date,
  COALESCE(current.category, previous.category) AS category,
  current.transactions,
  previous.transactions AS prev_transactions
FROM {{orders_filtered}} current
LEFT JOIN {{orders_comparison}} previous
  ON  current.date = previous.comparison_date
  AND current.category = previous.category

With all that hard work done, we can visulaise the data, either as a single value, with a comparison to its previous period using the comparison to target feature... {% big_value data="combined_data" value="sum(transactions) as transactions" comparison={ compare_vs="target" target="sum(prev_transactions)" text="Vs. Comparison" } /%}

...Or, the more interesting thing that couldnt be achieved otherwise, we can plot both current and previous together {% combo_chart data="combined_data" x="date" %} {% line y="sum(transactions) as transactions" /%} {% line y="sum(prev_transactions) as previous_transactions" options={ type="dashed" } /%} {% /combo_chart %}

Summary

Lets round up with some Pros and Cons

Pros

We can compare and plot data over time
We can do this for any comparison type. The logic would need tweaking, but theres nothing to stop you from say comparing to the prior year but matched to the day of the week, ie: 1st Monday in Jan 2025 to 1st Monday in Jan 2024 and so on
Less repetition. depending on how many charts you want, you could end up adding the filters and comparison options over and over, breaking the DRY (Dont Repeat Yourself) Principle. Here we apply our filters and date logic once in the initial inline sql

Cons

Complexity. Theres no arguing that this method isnt more involved, thats the tradeoff

Garlando/evidence_studio_date_comparison.md