Great question! This is a super common requirement in analytics—being able to see core metrics (like website visitors) broken down by a dimension (device type) and their week-over-week, month-over-month, and year-over-year changes all in one place. Let’s walk through practical implementations for the most common tools you’re likely using:

1. SQL (For Backend Data Aggregation) #

If your report pulls from a database, window functions like LAG() are your best friend. They let you "look back" at historical values for the same device type without messy joins. Here’s a concrete example for weekly visitor counts:

-- First, aggregate core data by device type and week
WITH weekly_device_visits AS (
  SELECT
    device_type,
    DATE_TRUNC('week', visit_timestamp) AS week_start_date,
    COUNT(DISTINCT visitor_id) AS total_visitors
  FROM website_traffic
  GROUP BY device_type, DATE_TRUNC('week', visit_timestamp)
)
-- Calculate comparative metrics
SELECT
  device_type,
  week_start_date,
  total_visitors,
  -- WoW: Current week vs prior week
  total_visitors - LAG(total_visitors, 1) OVER (PARTITION BY device_type ORDER BY week_start_date) AS wow_visitor_change,
  ROUND(
    (total_visitors - LAG(total_visitors, 1) OVER (PARTITION BY device_type ORDER BY week_start_date))::FLOAT 
    / NULLIF(LAG(total_visitors, 1) OVER (PARTITION BY device_type ORDER BY week_start_date), 0) * 100,
    2
  ) AS wow_percent_change,
  -- MoM: Current week vs same week 4 weeks prior (adjust for your month definition if needed)
  total_visitors - LAG(total_visitors, 4) OVER (PARTITION BY device_type ORDER BY week_start_date) AS mom_visitor_change,
  ROUND(
    (total_visitors - LAG(total_visitors, 4) OVER (PARTITION BY device_type ORDER BY week_start_date))::FLOAT 
    / NULLIF(LAG(total_visitors, 4) OVER (PARTITION BY device_type ORDER BY week_start_date), 0) * 100,
    2
  ) AS mom_percent_change,
  -- YoY: Current week vs same week 52 weeks prior
  total_visitors - LAG(total_visitors, 52) OVER (PARTITION BY device_type ORDER BY week_start_date) AS yoy_visitor_change,
  ROUND(
    (total_visitors - LAG(total_visitors, 52) OVER (PARTITION BY device_type ORDER BY week_start_date))::FLOAT 
    / NULLIF(LAG(total_visitors, 52) OVER (PARTITION BY device_type ORDER BY week_start_date), 0) * 100,
    2
  ) AS yoy_percent_change
FROM weekly_device_visits
ORDER BY device_type, week_start_date DESC;

Key Notes: #

• Use PARTITION BY device_type to ensure we only compare values for the same device.
• NULLIF() prevents division by zero errors if there were no visitors in the prior period.
• For monthly aggregation, swap DATE_TRUNC('week') with DATE_TRUNC('month') and use LAG(total_visitors, 1) for MoM.

2. Excel/Google Sheets #

For spreadsheet-based reports, use lookup functions to match the same device type and historical period. Let’s assume:

• Column A: Device Type
• Column B: Week Start Date
• Column C: Total Visitors

Add these formulas to calculate the metrics:

• WoW Visitors: =XLOOKUP(A2&B2-7, A:A&B:B, C:C, 0) (matches same device, same day last week)
• WoW % Change: =IFERROR((C2-D2)/D2*100, 0) (where D2 is the WoW Visitors value)
• YoY Visitors: =XLOOKUP(A2&B2-365, A:A&B:B, C:C, 0)
• YoY % Change: =IFERROR((C2-F2)/F2*100, 0) (where F2 is the YoY Visitors value)

For weekly aggregation, use WEEKNUM() to match the same week number and year:
=XLOOKUP(A2&WEEKNUM(B2)&(YEAR(B2)-1), A:A&WEEKNUM(B:B)&YEAR(B:B), C:C, 0)

3. BI Tools (Tableau, Power BI) #

Most modern BI tools have built-in functions to simplify this:

Tableau #

1. Create a calculated field for WoW Visitors:

   LOOKUP(SUM([Total Visitors]), -1)
   

   (This looks back 1 row in the view—make sure your view is sorted by date and grouped by device type.)
2. WoW % Change:

   (SUM([Total Visitors]) - [WoW Visitors]) / [WoW Visitors]
   

3. For YoY, use DATEADD() in a calculated field:

   SUM([Total Visitors]) - LOOKUP(SUM([Total Visitors]), DATEADD('year', -1, [Week Start Date]))
   

Power BI #

Use CALCULATE() with time intelligence functions:

1. WoW Visitors:

   WoW Visitors = CALCULATE(SUM('Traffic'[Total Visitors]), DATEADD('Date'[Date], -7, DAY))
   

2. WoW % Change:

   WoW % Change = DIVIDE(SUM('Traffic'[Total Visitors]) - [WoW Visitors], [WoW Visitors])
   

3. YoY Visitors:

   YoY Visitors = CALCULATE(SUM('Traffic'[Total Visitors]), SAMEPERIODLASTYEAR('Date'[Date]))
   

Pro Tips for All Tools: #

• Handle edge cases: Use COALESCE() (SQL) or IFERROR() (spreadsheets/BI) to replace NULLs with 0 or "N/A" where historical data doesn’t exist.
• Align time periods: For YoY, make sure you’re comparing the same week/month (account for leap years or 53-week years if needed).
• Keep the report clean: Use conditional formatting (e.g., green for positive changes, red for negative) to make trends easy to spot.

内容的提问来源于stack exchange，提问作者wildWill