Skip down to the research record

Pipeline trend report: 6 steps to compare successive totals

Quota Forecast Editorial Team · Published · 11 min read

Use Metabase's Offset function in the Summarize step, such as Offset(Sum([Total]), -1), to pull the previous row's total for comparison.

Pipeline trend report: how to compare successive totals

The distinction between a row comparison and a calendar-period comparison hinges on what the previous value actually references. A previous-row calculation looks at the immediately preceding available data point in the grouped result set, not necessarily the prior day or week on the calendar. When you build a pipeline trend report, the system groups your records according to the order you specify in the breakout settings. Because Offset can only reference the rows you have, the previous row may be several days or weeks earlier than the current one.

Metabase documents this behavior through its Offset function. According to Metabase, an expression like Offset(Sum([Total]), -1) gets the value of Sum([Total]) from the previous row. This syntax retrieves the calculated total from the row directly above the current one in the query results. It does not calculate the sum for the previous calendar date; it simply fetches the already-computed value from the adjacent row in the dataset returned by the query.

Key takeaways for how do I compare successive totals in a pipeline trend report?

  • Check breakout order before calculating differences. Because Offset refers to other rows, the sequence of groups in the Summarization step determines which total appears next, according to Metabase.
  • Verify row availability for offset references. The function can only reference the rows you have, which means it can produce correct but unexpected results when data is missing, according to Metabase.
  • Distinguish calendar days from 24-hour intervals. When doing arithmetic on a timestamp with time zone value in a time zone that recognizes DST, adding or subtracting interval '1 day' does not necessarily have the same result as adding or subtracting interval '24 hours', according to PostgreSQL.
  • Label comparison types explicitly. Distinguish whether a trend report compares adjacent available rows or fixed calendar periods to avoid misinterpreting gaps.
  • Audit source field definitions. Confirm that process-stage identifiers and completion timestamps are labeled precisely rather than treated as deal close dates.

Steps one and two: retrieve deals and identify stage context

Retrieving deal records requires understanding the specific API endpoint and its pagination limits before any grouping or comparison logic applies. To request a list of all deals, make a GET request to /crm/v3/objects/deals, according to HubSpot. This retrieval step is distinct from interpreting where those deals sit in your sales process. Deals are tracked through your sales process in pipeline stages until they’re won or lost, according to HubSpot. When pulling data for a trend report, you must account for how many objects can be fetched at once. For the CRM Search API, each page supports up to 200 objects, according to HubSpot. If your dataset exceeds this limit, you must handle pagination to ensure the total count reflects all records rather than just the first batch.

Identifying stage context means distinguishing between the current position of a deal and the timestamp of when a process ended. In Microsoft Dynamics 365, the Opportunity Sales Process entity contains specific fields that clarify these states. One field provides the unique identifier of the active stage for the Business Process Flow instance, according to Microsoft. This identifier tells you which step the opportunity is currently occupying. Another separate field records the date and time when the Business Process Flow instance is completed, according to Microsoft. It is critical to label these fields precisely. The completion time indicates when the flow finished; it does not automatically equate to the deal close date unless your system defines it that way. See close date change log for a related check.

The following table summarizes the documented behaviors for these initial steps across the relevant publishers. Each row describes what the source explicitly documents regarding retrieval limits or stage identification.

PublisherDocumented BehaviorSource Constraint
HubSpotRequests all deals with GET to /crm/v3/objects/dealsEndpoint for the full deal list
HubSpotTracks deals in pipeline stages until won or lostScope limited to HubSpot CRM documentation
MicrosoftIdentifies active stage by unique identifierSpecific to Business Process Flow instance
MicrosoftRecords date/time when flow instance completesDistinct from active stage identifier
HubSpotDefines the endpoint for retrieving deal listsScoped to the deal list endpoint
MicrosoftDistinguishes process completion from active stageFields belong to OpportunitySalesProcess entity

Verify that your extraction script handles pagination for the endpoint you use when building the base dataset for your trend report. Confirm that the stage identifier you use maps to the active process step rather than a historical completion event.

Steps three and four: sort inputs and order grouped totals

Deterministic row positioning requires controlling how source data is ordered before any comparison logic runs. If the underlying list shifts between report generations, a previous-row reference points to a different record each time, breaking the trend line. You must lock the input order and the aggregation structure separately.

Step 3: Sort visible inputs Before grouping, establish a stable sequence for the raw deal records. Sort the view by one or more columns before you export it, so the row order is explicit. Select a field with high temporal stability, such as creation date or stage entry date, to serve as the primary sort key. This ensures the Nth row in your source export corresponds to the same logical position in the next export, provided no new records are inserted above it. If your CRM allows multiple sort criteria, apply a secondary sort on deal ID to resolve ties. Do not rely on unsorted views, which have no guaranteed row order.

Step 4: Order grouped totals Once the source rows are stable, define the breakout hierarchy in your BI tool. Metabase sorts by the first group, then partitions by any additional breakouts according to Metabase. This means the "previous" row for a given category depends entirely on where that category sits within the partition defined by the additional breakouts. For example, if you want to see the counts of orders by product category over time, and the counts by product category for the previous period, you should first group by Created At, then by the product category according to Metabase. Placing the time dimension first sorts the rows by period, and the category breakout then forms the partitions. The Offset function then references the previous row within that specific partition. If you reversed the order and grouped by category first, the time breakout would form the partitions, so the previous row would be another category in the same period, which is a fundamentally different calculation. Choose the hierarchy that matches your analytical question: are you comparing periods within a category, or comparing categories within a period?

StepTool ActionGrouping Logic
3Sort source view columnsPrimary sort on stable temporal field; secondary sort on unique ID to resolve ties
4Define breakout hierarchyTime dimension first as the sort key; category breakout second to create partitions
4Apply Offset in SummarizeReferences previous row within the current partition, not across all rows globally

Steps five and six: compare rows and inspect elapsed time

To compare successive totals in a pipeline trend report, use the Offset function to retrieve the value of an expression in a different row, according to Metabase. The documentation specifies that you can choose the previous row by using -1, or the next row by using 1, according to Metabase. This mechanism allows you to pull the preceding total directly into your current calculation without assuming a fixed calendar interval.

When interpreting the time difference between those rows, distinguish timestamp subtraction from simple date arithmetic. Subtraction of date or timestamp values with the “-” operator returns the number of days (24-hours) and hours/minutes/seconds between the values, making the same adjustments, according to PostgreSQL. If your report spans a DST transition, check the subtraction result for the time zone in use.

The following table outlines how these two mechanisms interact when comparing pipeline totals:

Comparison TypeMechanismTime Zone Impact
Row-to-row comparisonUses Offset with -1 for the previous rowNone unless elapsed time is also calculated
Elapsed time calculationSubtracts timestamps using the “-” operatorReturns days (24-hours) plus h/m/s with DST adjustments

A previous-row value does not automatically represent the immediately preceding calendar day or week; it represents whatever row precedes it in the sorted dataset. Inspect the gaps between your data points before interpreting the delta. If a deal stage changed during a DST shift, verify the elapsed-time result for the time zone in use.

Verify that your grouping order places time as the primary breakout if you are following a time-and-category example. The Offset function references existing rows only, so missing periods are skipped and the previous row is the last available one. Check for gaps in your source data first. If the previous row is three weeks ago rather than yesterday, the comparison remains valid as a row comparison but invalid as a weekly trend check.

Pipeline trend reference table by publisher

The following table maps each documented capability to its relevance for comparing successive pipeline totals. Each row cites the specific feature described in the source documentation.

PublisherDocumented FeatureTrend Analysis Relevance
HubSpotTo request a list of all deals, make a GET request to /crm/v3/objects/deals HubSpotProvides the endpoint for retrieving source deal records used as input for trend calculations
MicrosoftUnique identifier of the active stage for the Business Process Flow instance MicrosoftIdentifies the active process stage field, distinct from completion timestamps, for context labeling
PipedriveLike other list views in Pipedrive, you can customize and sort your visible columns PipedriveAllows inspection of source view column ordering before grouped totals are calculated
MetabaseOffset can only be used in the query builder’s Summarize step (you cannot use Offset to create a custom column) MetabaseDefines where previous-row comparisons must occur within the query structure
PostgreSQLWhen doing arithmetic on a timestamp with time zone value in a time zone that recognizes DST, this means that adding or subtracting (say) interval '1 day' does not necessarily have the same result as adding or subtracting interval '24 hours' PostgreSQLHighlights elapsed-time calculation differences when inspecting gaps between rows in DST zones

Review the grouping order and date assumptions in your existing report against these capabilities. Label whether your comparison references the preceding available row or the preceding calendar period. See pipeline report filters for related checks.

Keep the source fields, grouping order and date assumptions on file

Before you interpret a change between two pipeline totals, label the comparison type in your notes. Write down whether the report compares the preceding available row or the preceding calendar period. If a week is missing from your data set, the previous-row calculation skips that gap rather than measuring a fixed seven-day interval. Record the specific dates present in your query so you know exactly which total sits behind the current one.

Audit your grouping order next. The sequence of breakouts determines which rows appear adjacent to each other. If you group by month before category, the previous row for a given category is that category's total in the prior month. Check the actual row positions in your output before assuming the link holds.

FAQ: interpreting changes between pipeline totals

How do I compare totals in a pipeline trend report?

Use the Offset function within the Summarize step of the query builder to retrieve the preceding total. This approach allows you to reference the immediately preceding data point for comparison purposes. Ensure your query structure supports this specific summarization operation before applying it to your dataset.

Does the previous row always represent the previous calendar period?

No, the previous row does not necessarily correspond to the previous calendar day or week. According to Metabase, Offset can only reference the rows you have, which means it can produce correct but unexpected results. If there are gaps in your time series data, the offset will reference the last available row rather than a fixed calendar interval. Inspect your data for missing periods before interpreting these values as chronological steps.

Can I use Metabase Offset in a custom column?

No, you cannot use Offset to create a custom column in the query builder. According to Metabase, Offset can only be used in the query builder’s Summarize step. This limitation requires you to place the calculation within the aggregation phase of your question building process. You must structure your query so that the offset calculation occurs during the summarization of your selected fields.

Which field identifies the active Dynamics business process stage?

The unique identifier of the active stage for the Business Process Flow instance serves this purpose. According to Microsoft, this field tracks the current position within the defined process flow. It is distinct from completion timestamps. Use this specific identifier when filtering or grouping records by their current workflow state.

Is adding one day equivalent to adding 24 hours in my PostgreSQL time zone?

In time zones that recognize daylight saving time, adding an interval of one day does not necessarily equal adding twenty-four hours. According to PostgreSQL, arithmetic on a timestamp with time zone value in a DST-recognizing zone shows that adding or subtracting interval '1 day' does not necessarily have the same result as adding or subtracting interval '24 hours'. Check your specific time zone configuration to understand how these operations resolve in your environment.

Sources