Skip across the chapter
Quota Playbook

Offset mistakes: 3 checks for weekly rep comparisons

Quota Playbook Editorial Team · Published · 12 min read

Compare rep weeks with Offset by checking for missing weeks, confirming breakout order, and using the correct sign in the Summarize step.

Key takeaways

  • Define the week, metric and rep list before writing the query.
  • Check that neighboring rows belong to the same rep and adjacent weeks.
  • Build Offset in Summarize and check its direction.
  • Confirm the latest week is present and check a result by hand.

One week's numbers show where a rep is. Two weeks side by side show which way the numbers are moving, and that gives you something specific to talk about. A query tool can build this kind of comparison with an offset function, which reads a value from another row and places it next to the current row.

The function is simple to write and easy to get wrong. A small slip can put last week's number beside the wrong week, or point a rep's row at someone else's numbers. The rep then spends the meeting explaining a change that never happened, and the conversation you planned gets pushed aside.

This post covers three mistakes to avoid. The examples use the Offset function in Metabase, because its documentation includes worked examples that show what the function returns. The ideas carry over to other query tools, though the details will differ.

Set the definitions before you write the query

Set the definitions first: the week boundaries, the metric, and the list of reps. An offset can only compare what the query already defines, so write these down before you write any Offset expression. That way the comparison means the same thing each time you run it.

Start with the week itself. Decide which day begins the week, and decide whether a week counts as complete when you run the report. A report run in the middle of the current week shows a partial week. Comparing a partial week with a full one can make a rep look like they dropped off, when the only difference is when you ran the report.

Next, the metric. Pick one number for the comparison, such as the total for the week, and use the same definition every time. If the definition changes between weeks, the change you see may come from the definition rather than from the rep. Write the metric definition as one plain sentence, covering what counts toward the total and what does not. A sentence like that is quick to reread before a meeting.

Last, the rep list. Decide whether a rep who was on leave, or who joined partway through the period, appears in the comparison. Make that choice once and apply it to everyone. A rep with no rows for a given week creates a gap, and the first mistake below explains what a gap does to an offset.

Keep these three definitions in a short note at the top of the query or in your meeting notes. When someone asks why a number moved, you can point to the definition instead of guessing.

Mistake 1: the previous row is not always last week

The previous row is not always last week, because Offset works on rows, not on calendar weeks. The number you type tells the function how many rows to move. The function does not check whether the row it lands on is the period you have in mind.

Metabase's documentation describes the offset number in row terms: "For example, -1 for the previous row, or 1 for the next row." ( Offset | Metabase Documentation)

The same page shows the effect with daily data. In one example, "The value in the Previous column for October 4th is 3, which is the value of the previous row." It then points out the gap: "While correct based on the data, the previous row is October 2nd; there's no data for the previous day, October 3rd." The result is correct for the rows that exist. The "previous" value, though, comes from two days earlier, because there is no row for October 3rd. The function did what it was told. The calendar was not what the reader expected.

Read that example as a checklist. Ask which row the function treats as the previous row, what calendar date that row carries, and whether the gap is in your data or only in your reading of the output.

Weekly comparisons have the same weakness. Suppose a rep had no closed deals in one week, so no row exists for that week. An offset of -1 reaches back past the gap and returns the number from an older week. If you read that number as last week, you are comparing the wrong two weeks, and the change you describe to the rep may not be real.

The fix belongs in the data or the grouping, not in the offset. Changing -1 to -2 to skip a gap is a guess, since it assumes the missing week is the only one.

Before the meeting, check for this directly. List each rep's weeks in order and look for any week that is missing from the results. If one is missing, the number beside the following week came from further back than it appears. Then decide what a missing week means in your data. It could mean the rep had no activity, or it could mean the data did not load. Those two cases call for different handling, so settle which one applies before you draw a conclusion from the gap.

In the one-to-one itself, do not describe a week-over-week change until you have confirmed that both weeks are adjacent in the data. If you cannot confirm it, say so, or leave that rep out of the comparison for now. "I want to confirm this is the prior week before we talk about it" is a better opening than presenting a change that turns out to be a gap in the data.

Mistake 2: the order of your breakouts changes the result

Set the order of your breakouts on purpose, because Offset looks at neighboring rows, and the breakouts decide which rows are neighbors. Metabase's documentation states the rule plainly: "Because Offset refers to other rows, the order of the breakouts matters (the breakouts are the groups in the "Group By" section in the Summarization step)." ( Offset | Metabase Documentation)

A breakout is one of the groupings you add in the Summarize step, such as rep or week. Each combination of those groupings becomes one row in the results, and Offset works on those rows.

For a rep comparison, you want the rows for each rep's weeks to sit together in time order. Then the row above a given week is that same rep's week before it.

Here is a simple way to picture the difference. Suppose two reps each have three weeks of results. If the rows are grouped by rep and then by week, each rep's weeks sit in one block, and the row above a rep's second week is that rep's first week. The data is the same in both cases. The neighbors are not. The number still appears and still looks like a valid value, which is why this mistake is easy to miss.

Check it with a rep whose numbers you know. Pick someone you can verify by hand, find their week in the results, and look at the row the offset pulls from. If that row belongs to another rep, or to an earlier period than you expected, change the order of the breakouts and run the query again. Do not assume the rows come back in the order you want. Read them in the results table before you use any figure from them.

After any change to the breakouts, run the same rep check again. Changing one grouping can shift the neighbors for every rep in the table, so a fix that works for one rep can break another. Check the other reps before you show the comparison to anyone.

Mistake 3: using the wrong sign or building Offset outside Summarize

Use -1 to reach the previous row; it represents the previous week only when that row is the adjacent week, and build Offset in the Summarize step rather than as a custom column. When a comparison looks wrong, check those two things first.

The sign matters. Metabase's documentation describes the number this way: "The rowOffset is the number relative to the current row." ( Offset | Metabase Documentation) Its example reads: "Example: Offset(Sum([Total]), -1) would get the value of Sum([Total]) from the previous row." ( Offset | Metabase Documentation) A negative number reaches back toward earlier rows. A positive number reaches forward. If you type 1 where you meant -1, the column shows the next row's value, which represents next week only when that row is the adjacent week. For a comparison of change over time, that is the wrong direction, and the number can look plausible if you do not check it against a week you know.

Where you add the offset also matters. The docs say: "Offset can only be used in the query builder's Summarize step (you cannot use Offset to create a custom column)." ( Offset | Metabase Documentation) Put the offset inside the Summarize step, wrapped around the aggregation it refers to, as the docs' example does with Sum([Total]).

The docs also show a three-period average built from offsets: "The above expression adds up this period's total, plus the totals for the previous two periods (offset by -1 and -2), and then divides by three to get the average across those periods." ( Offset | Metabase Documentation) An average like this can smooth a rep's weekly numbers, because it covers three periods instead of one. Two things need checking. Each offset needs its own sign, so the -1 and -2 both point backward. And the average is only as reliable as the rows each offset reaches. If a week is missing, the first mistake applies again.

To check a single offset or an average, pick a rep with known numbers for the weeks involved. Work out the value by hand and compare it with the result. If they match, continue checking the other reps before using the comparison. If they do not, check the signs first, then look for gaps in the weeks, then check the breakout order.

If the sign check fails, change the number, run the query, and repeat the same check on that rep. Each change should be followed by the check, not replaced by a guess that the next result will look right.

Illustrative example: adjacent weeks

Suppose a rep's total is 40 for the previous week and 50 for the current week. These figures are illustrative. With those adjacent weeks in order, Offset(Sum([Total]), -1) beside the current week returns 40. The increase is 10. Confirm the rep and week on the source row before discussing that increase.

Confirm the data has refreshed before you compare weeks

Confirm the data has refreshed before you compare weeks, because a comparison built on older data will not include the most recent week. Refresh timing is separate from the offset itself, and it is easy to overlook, since the query runs without error even when the data is a week old.

Look at the last date in the results and check that it is the week you expect. If the last date is a week behind, the latest week is missing, so this is not the current week-over-week comparison; changes between the weeks present may still reflect the rep's results.

If your data comes through Power BI, its documentation points to a separate page for the details: "For more information about data refresh, including limitations on refresh schedules, see Data refresh." ( Configure scheduled refresh - Power BI | Microsoft Learn) Read that page before you assume a schedule will have your latest week ready in time for a meeting. Check the limits it describes against your own schedule, not against your memory of how the schedule was set up.

A refresh that finishes without including the latest week is a data problem, not a formula problem. Fix it in the data load, then rerun the comparison.

Run a quick test before the one-to-one

Run one test on a rep whose numbers you already know, then apply the same checks to everyone else. The test takes a few minutes and can catch the problems described above before they reach the meeting.

Work through the checks in this order. First, confirm the refresh and the last date in the results. Second, list each rep's weeks and look for gaps, which is the first mistake. Third, pick one rep and confirm that the row above a given week belongs to the same rep and the right earlier week, which is the second mistake. Fourth, compare one offset value, and one average if you use one, with a figure you calculated by hand, which covers the third mistake.

Keep the week definitions the same each time you rerun the comparison, so that this week's version lines up with last week's. If a definition has to change, note the change in your meeting notes so the rep is not surprised by a shift in the numbers.

Write down the date you ran the checks and the rep you used for the hand calculation. If someone asks later why a comparison changed, that note shows what was verified and when. Use the sales coaching logs checklist to review the record.

In the meeting, show both weeks' numbers next to the change, and name the weeks out loud. Ask the rep to confirm the figures from their side. Showing the source weeks keeps the conversation on what happened, rather than on whether the chart is right. If the rep says a number is wrong, check the rows behind it before you answer, because a gap or a wrong neighbor can cause an incorrect comparison. Put the comparison into your one-to-one agenda.

How to talk about a gap or a wrong number

Talk about a gap or a wrong number as a problem in the comparison, not in the rep. If you find one during the check, say so plainly: the week is missing from the data, or the comparison pulled the wrong row. Then describe what you are doing about it.

Avoid presenting a change as fact until you have confirmed the rows behind it. A change that turns out to be a data gap damages the trust you are trying to build in the meeting, and it is harder to repair than a short delay in showing the comparison.

Ask the rep about their week if the data is clean but the number still surprises you. The rep may know about a deal that closed late, a leave day, or a customer issue that the query cannot see. The comparison gives you a starting point for the question, and the rep's answer gives you the context.

After the meeting, write a short note on any gap you found and what you did about it. Fix the cause in the query or the data load first, then rerun the comparison and check the same rep again, so the next one-to-one starts from a verified table.

What to take into the one-to-one

Today, run the checks above on a rep whose weekly totals you know, and record the result before preparing the meeting comparison.

Questions before the one-to-one

Does a matching hand calculation validate every rep?

No, a match checks that result; continue checking the other reps before using their comparisons.

What if the rep disputes a figure?

Check the rows behind the figure before answering, then ask the rep for context if the data is clean.

Sources

  1. Bulk email timing: 3 facts before a coaching send
  2. Tiered commission plan: 4 steps for a 1:1 talk
  3. Seller daily time: 3 tasks that pull reps off selling
  4. Device and 2FA checks

More plays