Working with Groups

How grouping changes every calculation in the package

Grouping is the main dial

Every time-intelligence and segmentation function in ti respects dplyr::group_by(). There is no .by argument and no group parameter — you group the table before you call the function, and the calculation follows.

Ungrouped, mtd() gives you one month-to-date series for the whole company:

contoso::sales |>
  mtd(.date = order_date, .value = gross_margin, calendar_type = "standard") |>
  calculate() |>
  collect() |>
  arrange(date) |>
  select(date, gross_margin, mtd_gross_margin) |>
  head(5)

# A tibble: 5 × 3
  date                gross_margin mtd_gross_margin
  <dttm>                     <dbl>            <dbl>
1 2021-05-18 00:00:00         407.             407.
2 2021-05-19 00:00:00         711.            1118.
3 2021-05-20 00:00:00        1424.            2542.
4 2021-05-21 00:00:00       11339.           13881.
5 2021-05-22 00:00:00        5359.           19240.

Grouped by store, you get one independent series per store:

by_store <- contoso::sales |>
  group_by(store_key) |>
  mtd(.date = order_date, .value = gross_margin, calendar_type = "standard") |>
  calculate() |>
  collect()

by_store |>
  arrange(store_key, date) |>
  select(store_key, date, gross_margin, mtd_gross_margin) |>
  head(5)

# A tibble: 5 × 4
  store_key date                gross_margin mtd_gross_margin
      <dbl> <dttm>                     <dbl>            <dbl>
1        10 2021-09-16 00:00:00         747.             747.
2        10 2021-09-17 00:00:00           0              747.
3        10 2021-09-18 00:00:00           0              747.
4        10 2021-09-19 00:00:00           0              747.
5        10 2021-09-20 00:00:00           0              747.

The grouping columns are carried through into the result, so they are available to filter and join on afterwards.

The cumulative sum resets per group

This is the behaviour that makes grouping worth doing. Each group’s running total is independent — store 10’s month-to-date figure never picks up store 20’s revenue.

by_store |>
  filter(year == 2023, month == 6) |>
  arrange(store_key, date) |>
  summarise(
    month_total = max(mtd_gross_margin),
    .by = store_key
  ) |>
  arrange(desc(month_total), store_key) |>
  head(5)

# A tibble: 5 × 2
  store_key month_total
      <dbl>       <dbl>
1    999999     102900.
2       540       8736.
3       650       2134.
4       660       1849.
5       510       1548.

Multiple grouping levels

Group by as many columns as you need. The calculation is performed for every distinct combination:

contoso::sales |>
  group_by(store_key, product_key) |>
  mtd(.date = order_date, .value = gross_margin, calendar_type = "standard") |>
  calculate() |>
  collect() |>
1  filter(missing_date_indicator == 0) |>
2  arrange(desc(mtd_gross_margin), store_key, product_key, date) |>
  select(store_key, product_key, date, gross_margin, mtd_gross_margin) |>
  head(5)

1

Drop the zero-filled calendar rows so we are looking at real trading days.

2

Always sort before taking head(). A collected result has no guaranteed row order — the database returns rows in whatever order the query plan produced, so an unsorted head() gives you arbitrary rows that can change between runs.

# A tibble: 5 × 5
  store_key product_key date                gross_margin mtd_gross_margin
      <dbl>       <dbl> <dttm>                     <dbl>            <dbl>
1    999999         145 2023-06-22 00:00:00        9696.           18377.
2    999999         145 2021-12-30 00:00:00       16218.           16218.
3    999999        1915 2022-05-03 00:00:00       16036.           16036.
4    999999         576 2021-08-16 00:00:00       15854.           15854.
5       230        1939 2021-10-07 00:00:00       13703.           13703.

Be aware of the cost. Because ti completes the calendar for each group (see Calendar Completion and Missing Dates), the row count is roughly number of groups × number of days in that group’s active range. Grouping by two high-cardinality columns can multiply your result set considerably — one reason to keep the pipeline lazy and filter in the database.

Each group gets its own calendar range

A subtle and important point: ti does not force every group onto the same date range. Each group’s calendar is completed only within that group’s own first and last observed date.

Consider two stores, where store B opens two months after store A:

openings <- tibble::tibble(
  store      = c("A", "A", "A", "B", "B"),
  order_date = as.Date(c("2024-01-01", "2024-01-03", "2024-03-01",
                         "2024-03-01", "2024-03-05")),
  amount     = c(10, 20, 30, 40, 50)
)

openings |>
  group_by(store) |>
  ytd(.date = order_date, .value = amount, calendar_type = "standard") |>
  calculate() |>
  collect() |>
  summarise(
    first_date = min(date),
    last_date  = max(date),
    n_rows     = n(),
    .by = store
  ) |>
  arrange(store)

# A tibble: 2 × 4
  store first_date          last_date           n_rows
  <chr> <dttm>              <dttm>               <int>
1 A     2024-01-01 00:00:00 2024-03-01 00:00:00     61
2 B     2024-03-01 00:00:00 2024-03-05 00:00:00      5

Store B’s series starts in March, not January. It is not back-filled with two months of zeroes just because store A had data then.

This is the right default for retail and subscription analysis. A store that opened in March did not have a bad January — it had no January. Padding it with zeroes would drag down every average and make year-over-year comparisons meaningless.

Checking your grouping before you run

Because a ti object is a blueprint, you can inspect what it is about to do. Printing it reports the group count:

contoso::sales |>
  group_by(store_key) |>
  mtd(.date = order_date, .value = gross_margin, calendar_type = "standard")

── Month-to-date ───────────────────────────────────────────────────────────────

Function: `mtd` was executed

── Description: ──

This creates a daily `cumsum()` of the current month gross_margin from the
start of the standard calendar month to the end of the month

── Calendar: ──

• The calendar aggregated order_date to the day time unit
• A standard calendar is created with 1 groups
• Calendar ranges from 2021-05-18 to 2024-04-20
• 222 days were missing and replaced with 0
• New date column date, year, quarter, month was created from order_date

── Actions: ──

✔Aggregate

✖Shift

✖Compare

✖Proportion of Total

✖Count Distinct

store_key groups are in the table

── Next Steps: ──

• Use `calculate()` to return the results

────────────────────────────────────────────────────────────────────────────────

If the group count is not what you expected, you have caught the mistake before running a query over the full table.

Grouping and segmentation

The segmentation functions treat grouping differently: for abc(), the grouping columns define what is being ranked. Grouping by store ranks stores against each other by their contribution to the total.

contoso::sales |>
  group_by(store_key) |>
  abc(category_values = c(0.7, 0.96, 1), .value = gross_margin) |>
  calculate() |>
  collect() |>
  select(store_key, abc_gross_margin, prop_total, cum_prop_total, category_name) |>
  head(5)

# A tibble: 5 × 5
  store_key abc_gross_margin prop_total cum_prop_total category_name
      <dbl>            <dbl>      <dbl>          <dbl> <chr>        
1    999999         2193234.     0.535           0.535 a            
2       540           78124.     0.0191          0.554 a            
3       610           74604.     0.0182          0.573 a            
4       510           69455.     0.0169          0.589 a            
5        80           67881.     0.0166          0.606 a            

Change the grouping column and you change the question: group by product_key and you are asking which products drive margin, not which stores.

Key points

• Group with dplyr::group_by() before calling the function; there is no .by argument.
• Cumulative sums reset per group and never bleed across groups.
• Grouping columns are carried into the result.
• Each group’s calendar is completed within its own date range, so late-starting groups are not zero-padded backwards.
• Print the object to check the group count before executing.
• For abc(), the grouping columns define the population being ranked.