Working with Dates and Time

Most business analysis is time-based. Revenue trends, seasonal patterns, year-over-year comparisons—all require working with dates effectively. This guide covers the essential techniques for handling date and time data in Querri.

Why Dates Matter

Dates unlock the most valuable business questions:

Trends: Is revenue growing or declining?
Seasonality: When are our peak periods?
Comparisons: How does this quarter compare to last year?
Recency: What happened in the last 30 days?

Without proper date handling, you’re stuck with static snapshots instead of dynamic insights.

Date Formats Querri Understands

Querri recognizes most common date formats automatically:

Format	Example
ISO 8601 (preferred)	2024-03-15
US format	03/15/2024
European format	15/03/2024
Text month	15-Mar-2024
Full text	March 15, 2024
With time	2024-03-15T14:30:00

Extracting Date Parts

Pull out components from dates for grouping and filtering.

Common Extractions

"Add a year column from the order_date"
"Extract the month from each date"
"Get the day of week for each transaction"
"Add a quarter column"

Available Date Parts

Part	Example values	Use for
Year	2023, 2024	Year-over-year comparisons
Quarter	Q1, Q2, Q3, Q4	Quarterly reporting
Month	January, 1-12	Monthly trends
Week	1-52	Weekly analysis
Day	1-31	Daily patterns
Day of Week	Monday-Sunday	Weekly patterns
Hour	0-23	Intraday analysis

Combining Extractions

Create useful groupings:

"Add year and month columns for monthly grouping"
"Extract year, quarter, and month from the date"
"Add a year-month column like '2024-03'"

Filtering by Date

Focus on the time periods that matter.

Specific Periods

"Filter to March 2024"
"Show only Q4 2023 data"
"Keep transactions from 2024"
"Filter to weekdays only"

Date Ranges

"Filter to January through March 2024"
"Show data from 2023-01-01 to 2023-12-31"
"Keep orders between Black Friday and New Year"

Relative Dates

Filter based on “now” for dynamic reports:

"Filter to the last 30 days"
"Show data from the last 12 months"
"Keep transactions from this quarter"
"Filter to year to date"

Before and After

"Show orders from before July 2024"
"Filter to customers who joined after 2023"
"Keep transactions from the last week"

Time-Based Aggregations

Group data by time periods for trend analysis.

By Time Period

"Total revenue by month"
"Weekly order counts"
"Daily active users"
"Quarterly sales"

Choosing the Right Grain

Time grain	Best for	Data needed
Hourly	Intraday patterns, operations	At least a few days
Daily	Recent detailed trends	At least a month
Weekly	Balanced detail/readability	At least 3 months
Monthly	Business reporting	At least a year
Quarterly	Executive reporting	At least 2 years
Yearly	Long-term trends	3+ years

Fiscal Calendars

If your fiscal year differs from calendar year:

"Group by fiscal quarter (starting in April)"
"Add fiscal year column where fiscal year starts in July"

Period Comparisons

Compare time periods to understand change.

Month-Over-Month

"Compare this month's revenue to last month"
"Show month-over-month change in orders"
"Calculate MoM growth rate"

Year-Over-Year

"Compare 2024 to 2023 by month"
"Year-over-year revenue growth"
"Show this quarter vs same quarter last year"

Same Period Comparisons

"Compare this March to last March"
"Show Black Friday sales for the last 3 years"
"Compare Q4 across all years"

Period Tables

Create comparison views:

"Create a table with monthly revenue for 2023 and 2024 side by side"
"Show weekly sales with this week, last week, and same week last year"

Finding Date Patterns

Discover when things happen.

Seasonality

"Show average revenue by month to see seasonal patterns"
"Compare quarterly performance over multiple years"
"Which months have the highest sales?"

Day-of-Week Patterns

"Average orders by day of week"
"Compare weekday vs weekend sales"
"Which day has the most customer signups?"

Time-of-Day Patterns

"Show orders by hour of day"
"When do customers most often make purchases?"
"Average response time by hour"

Identifying Anomalies

"Show days with unusually high or low sales"
"Find weeks where orders dropped more than 20%"
"Highlight dates that are statistical outliers"

Date Calculations

Create new date-based columns.

Duration Calculations

"Calculate days between order and delivery"
"Add a column for customer tenure in months"
"How many days since last purchase for each customer?"

Date Arithmetic

"Add 30 days to each date"
"Find the first day of each month"
"Calculate the last day of each quarter"

Age and Recency

"Calculate age from birth date"
"Days since last activity for each user"
"How old is each open ticket?"

Rolling Calculations

Smooth out noise with moving windows.

Moving Averages

"Calculate 7-day moving average of sales"
"Add a 30-day rolling average column"
"Show weekly revenue with 4-week moving average"

Rolling Totals

"Calculate rolling 12-month revenue"
"Show 90-day cumulative orders"
"Add trailing 30-day total"

Why Use Rolling Calculations?

Smooth volatility: Daily data can be noisy; 7-day averages smooth it out
Spot trends: Rolling averages reveal underlying trends
Compare fairly: Rolling totals normalize for time period length

Handling Date Issues

Missing Dates

Time series often have gaps:

"Fill in missing dates with zero values"
"Are there any gaps in the date sequence?"
"Show which dates are missing from the data"

Date Format Inconsistencies

When dates aren’t recognized properly:

"Convert the date_string column to proper dates"
"Parse dates from the text column"
"Standardize all dates to YYYY-MM-DD format"

Timezone Considerations

"Convert timestamps to Eastern time"
"What timezone are these dates in?"
"Normalize all times to UTC"

Incomplete Periods

Be careful comparing partial periods:

"Exclude the current month (it's not complete yet)"
"Show only full weeks"
"Filter out partial quarters"

Common Date Patterns

Pattern 1: Monthly Trend Analysis

Step 1: "Extract year and month from order_date"
Step 2: "Calculate total revenue by year-month"
Step 3: "Create a line chart of monthly revenue"

Pattern 2: Year-Over-Year Comparison

Step 1: "Add year and month columns"
Step 2: "Pivot revenue by month (rows) and year (columns)"
Step 3: "Calculate YoY percentage change"

Pattern 3: Cohort Analysis

Step 1: "Extract signup month for each customer"
Step 2: "Calculate months since signup for each transaction"
Step 3: "Group by signup cohort and tenure month"

Pattern 4: Seasonality Detection

Step 1: "Calculate average revenue by month across all years"
Step 2: "Create a bar chart showing monthly averages"
Step 3: "Identify peak and low months"

Tips for Date Data

Verify date ranges first — Know what time periods your data covers
Check for gaps — Missing dates can skew analysis
Be explicit about time zones — Especially for data from multiple sources
Use complete periods — Don’t compare full months to partial months
Consider business context — Fiscal years, holidays, seasonal factors
Document your time grain — Make clear whether data is daily, weekly, etc.

Next Steps

With date skills, you’re ready for advanced time-based analysis:

Aggregating Data — Summarize by time periods
Forecaster Tool — Predict future values
Creating Visualizations — Line charts for trends
Dashboard Basics — Time-based dashboards