Calculations: Before or After Diving

Both Spectre cPlans and Dives support calculations to generate new columns. What is the impact of performing calculations before or after diving?

Spectre can perform detail-level calculations on-the fly. It uses the raw detail level data and expands it with the new calculation. This was previously possible using Integrator, but not using classic models.

Spectre can also perform post-summary calculations. After a dive aggregates columns, calculations can be added to the remaining rows only. This is equivalent to Diver calculations using classic models.

How do you tell Spectre which to do in a dive file?

When you use the column tag in a dive window, it refers to the detail level data.
When you use operation tags, for example, add, sort, and filter, these calculations are done after the dive summaries are complete.

For example, given a cPlan with the following calculation:

calc "Cost" `sum(value("Price") * value("Quantity"))`

A dive using that cPlan could include the following window with the calculated Cost column:

window {
  dimension "Region"
  column "Cost"
  
  add "Target" `value("Cost") * 1.5`
  filter `value("Target") – value("Cost") > 100`
  add "row" `row_number()`
}

This dive is on the detail level, grouping records by region and computing the cost. This results in a summary table of region and cost data in memory. This is followed by other window operations—run after the summaries, that is, against the summary table in memory. The window operations:

Add a new target column
Run a filter with an expression that includes that new column
Add a row number column

The order specified in the window block is the order of the operations at run time. Each operation completes before the next one starts.