Calculating Precise Time-based Year-over-Year and Period-over-Period Comparisons

Background Requirements

BI's built-in year-over-year and period-over-period comparison functions can only calculate to daily granularity at the finest level, and time macros can only be precise to days. However, in many cases, users need to connect directly to databases to count real-time data for the current day and compare it with data from the same time period yesterday or other dates. For example, if it's currently 3:30 PM, they need to count data from midnight to 3:30 PM today, then compare it with data from midnight to 3:30 PM yesterday.

Prerequisites

1. Direct connection dataset.

2. The time field format in the dataset is datetime (or timestamp).

Approach

The calculation results in the card's value column cannot be used for secondary calculations, so we need to create new calculated fields to separately count data for fixed time periods for today and yesterday, then create new fields to calculate the year-over-year and period-over-period growth rate.

Implementation Method

Using MySQL database as an example.

1. Create a new calculated field to count today's tasks using a formula (there are multiple ways to write the formula)

sum(
  case when date([modification_time])=curdate()
  then 1 else 0
  end
)

2. Create a new calculated field to count yesterday's tasks using a formula (from 0:00 to yesterday's current time point)

sum(
case when date([modification_time]) =date_sub(curdate(),interval '1' day) and [modification_time]<=date_sub(now(),interval '1' day) then 1
else 0
end)

3. Create a new calculated field to calculate the growth rate.

4. Drag all three new fields to the value column.