Note: The quick test is available to everyone; only logged-in users have their progress saved.
Who this is for
- BI Analysts and data folks who build dashboards or reports with date-based metrics.
- Anyone who needs to show trends, seasonality, or growth over time without misleading viewers.
Prerequisites
- Basic understanding of measures and dimensions (e.g., revenue, orders, dates).
- Comfort with simple aggregations (SUM, COUNT) and grouping by dates.
- Familiarity with a spreadsheet or BI tool that can chart time series.
Why this matters
In BI, most questions include time: Are we growing? When did the drop start? Is it seasonal? Time series visuals influence decisions on staffing, inventory, marketing, and budgets. Getting it wrong can hide problems or create false alarms.
- Real tasks: weekly KPI dashboards, YoY performance, forecasting seasonality, analyzing campaigns/events, monitoring outages.
Concept explained simply
A time series is just a metric measured repeatedly at consistent intervals (hour, day, week, month).
- Granularity: the interval (e.g., daily). Pick one and stick to it for each chart.
- Regular vs. irregular: regular has every period present; irregular has gaps. Visualize time with a continuous axis.
- Missing periods: some days are absent or have zero. Decide whether to display as 0, null (gap), or filled via method.
- Seasonality: repeated patterns (weekly, yearly). Smoothing helps reveal the pattern (e.g., 7-day moving average).
- Rolling windows: moving averages or sums over previous N periods to reduce noise.
- Cumulative vs per-period: cumulative keeps increasing; per-period resets each period. Never mix them in one axis.
- Time zones and calendars: align data to a business timezone and fiscal/ISO weeks before charting.
Mental model
Think of time as a ruler with evenly spaced marks. Your data points should sit on those marks. If some marks have no data, you still show the mark; if you combine multiple rulers (week vs month), convert them onto one consistent ruler before plotting.
Core rules for clean time series visuals
- Use a continuous time axis with evenly spaced periods; include missing periods (as gaps or zeros) instead of skipping dates.
- Aggregate intentionally: sums for counts, averages for rates, medians for skewed data.
- Choose the right granularity for the question; roll up if daily is too noisy.
- Do not mix cumulative and per-period metrics on the same axis.
- Label time clearly: timezone, fiscal/ISO week if relevant, and period boundaries.
- Smooth responsibly: show both raw and smoothed lines, and state the window (e.g., 7-day MA).
- Handle seasonality: compare same weekdays or use YoY for monthly/seasonal effects.
- Avoid dual axes for different metrics; prefer normalization or separate panels.
- Annotate major events (launches, outages, price changes).
Worked examples
Example 1 — Daily sessions with gaps
Problem: Your export has 2025-01-03 missing.
date,sessions 2025-01-01,120 2025-01-02,98 2025-01-04,140 2025-01-05,160
Wrong: Plot only available dates; the line jumps and hides the gap.
Fix: Reindex to daily. Insert 2025-01-03 as 0 (if a true zero) or null (if unknown). In dashboards, pick a continuous date axis and enable "show missing items as 0/null". Show a thin 7-day MA if daily is noisy.
Example 2 — Cumulative vs per-month revenue
month,revenue,cumulative_revenue 2025-01,10000,10000 2025-02,9000,19000 2025-03,12000,31000
Wrong: Plot cumulative (ever-increasing) and per-month revenue on one axis. The cumulative dwarfs monthly bars.
Fix: Use separate charts, or pick one representation. For growth, use cumulative only. For month performance, use per-month only. If comparing to last year, use per-month with YoY percent.
Example 3 — Weekly seasonality and smoothing
Daily orders have strong weekday/weekend pattern, masking trend.
Fix: Add a 7-day moving average. Keep the raw daily line light; make the MA bold. Note the smoothing window in the legend. If you need comparability, align weeks by ISO week.
Example 4 — Time zone alignment
Support tickets are stored in UTC, business runs in America/New_York. Spikes appear around midnight UTC.
Fix: Convert timestamps to the business timezone before aggregating by day. If teams operate globally, show regional panels or normalize to each region's local day.
How to prepare time series data
- Confirm date/time types (true date or timestamp, not text).
- Choose a target granularity (hour/day/week/month) based on the question.
- Generate a complete calendar for that granularity and join your data to it.
- Aggregate with the correct function (SUM, AVG, MEDIAN). Decide how to treat missing periods (0 vs null).
- Handle outliers explicitly (winsorize, annotate, or separate).
- Optional: create smoothed features (rolling mean/sum) and YoY/DoD/ WoW deltas.
Visualization patterns that work
- Line for continuous measures; bar/column for per-period totals; area only when cumulative or when zero baseline makes sense.
- Small multiples for comparing regions or segments instead of dual axes.
- Highlight recent window with a subtle band; annotate major events.
- Use consistent colors for the same metric across charts and time.
Exercises
Do these to practice. Solutions are included in expandable blocks. Check off steps as you complete them.
Exercise 1 — Resample daily orders and visualize
Data:
date,orders 2025-01-01,10 2025-01-02,8 2025-01-04,13 2025-01-05,0
- Decide: is 2025-01-03 a true zero or missing?
- Create a daily calendar including 2025-01-03.
- Aggregate and fill the missing day appropriately.
- Make a line chart with a 7-day moving average.
Exercise 2 — Fix cumulative confusion
Data:
month,revenue,cumulative_revenue 2025-01,10000,10000 2025-02,9000,19000 2025-03,12000,31000 2025-04,8000,39000
- Plot revenue per month correctly.
- Show cumulative growth in a separate view.
- Add a simple MoM % change to the monthly chart.
Exercise 3 — Smooth and annotate events
Data:
date,signups,event 2025-03-01,200, 2025-03-02,210, 2025-03-03,205,Campaign A launch 2025-03-04,250, 2025-03-05,245, 2025-03-06,260, 2025-03-07,190,Site outage 2025-03-08,215, 2025-03-09,220, 2025-03-10,230,
- Create a daily line and a 7-day moving average.
- Annotate the launch and outage.
- Write one sentence interpreting the effect.
Exercise completion checklist
- Continuous date axis with no skipped periods.
- Correct aggregation and clear granularity.
- No mixing cumulative and per-period on one axis.
- Smoothing window documented (if used).
- Relevant event annotations present.
Common mistakes and self-check
- Skipping missing dates, making lines jump. Self-check: count periods between min and max; does it match the plotted points?
- Mixing cumulative and per-period in one plot. Self-check: does one line always go up? Separate if yes.
- Using daily for low-volume metrics, causing random spikes. Self-check: does weekly aggregation reveal a clearer trend?
- Dual axes to force correlation. Self-check: if you normalize both to index=100, is the relationship still obvious?
- Wrong timezone/fiscal week. Self-check: do midnight spikes align with timezone boundaries? Are week labels ISO/fiscal as intended?
- Over-smoothing hides events. Self-check: compare raw vs smoothed; ensure key peaks remain visible.
- Truncated y-axis exaggeration. Self-check: does the y-axis start at zero when showing volumes/bars? If not, clearly indicate the cut.
Practical projects
- Company KPI timeline: Build a dashboard with daily sessions, weekly conversion rate, and monthly revenue, each with proper granularity and annotations.
- Seasonality study: For a full year of orders, show weekday patterns and a 7-day moving average. Add a YoY monthly comparison.
- Event impact analysis: Before/after a product launch, compare 14-day windows with confidence bands or simple percent-lift notes.
Learning path
- Get date basics right: types, parsing, and calendars.
- Pick granularity and aggregate correctly.
- Add seasonality tools: moving averages, YoY/WoW.
- Annotate events and clarify timezones/fiscal calendars.
- Practice with diverse datasets (high/low volume, seasonal/non-seasonal).
Next steps
- Redo one of your existing dashboards to fix granularity, gaps, and annotations.
- Create a small template with calendar join, rolling metrics, and YoY fields for reuse in future analyses.
- Take the quick test to check your understanding.
Mini challenge
You have daily add-to-cart events for 90 days with weekend dips and a price change on day 45. Produce two charts: (1) raw daily with 7-day MA and an annotation for the price change; (2) weekly aggregated totals with a note on week definition used. Write two bullets on insights: seasonality, and price change effect.
Quick test
Ready to check your understanding? Take the quick test below.