Choosing "Formula result" in the Aggregation block means that, after splitting/aggregating values, the value in each cell is determined by the formula applied to other values in the same aggregated row, rather than the sum/mean/whatever of the relevant unaggregated values in that field.

Imagine you had a sales database:


Product Units Unit price Total (formula)
Widget 1 13 1*13 = 13
Sprocket 3 5 3*5 = 15


If you aggregate this into a single summary record for all products, you get the following for the non-formula fields, using the default Sum (which in this case doesn't make sense for "unit price"):

Product Units Unit price
All 4 18


And for the aggregated Total you can either choose the "Sum" of the unaggregated Totals (13 + 15, i.e. 28), or "Formula result" (4 * 13 = 52). In this case you'd probably want Sum, but there are plenty of cases where you want to apply the formula to the aggregated data, rather than aggregate the original formula values.

Regarding formulas as static values, this is indeed an important distinction for operations like pivot & de-pivot.

By default, formulas are NOT passed down between blocks - only static data. So if you add a formula in a Field Organiser, the resulting data passed down will be static. This means that "Formula result" won't be available as an option in Aggregate, and it means that pivot / de-pivot will operate as expected with the static results of your formulas.

To change this, in each block's tools menu, choose "Preserve formulas in output". The fields will be highlighted in blue in the preview table. This means these formulas are "live". If you now transform the data downstream, the formulas will be re-evaluated using the newly transformed data. For some cases, if the formulas refer to fields that no longer exist (e.g. due to pivot/de-pivot), those cells will be null, and a warning will appear in the block.

You can use Field Organiser if you want to preserve some but not all fields as formulas, by un-ticking the "formula" option for each field.

Hi Steve,

THanks for that. I understand what you mean now with the aggregates - it is especially important when calculating percentages!!!

I am a bit confused with the aggregated unit price though.....wouldn't it be 18?

Also I seem to have "Preserve formulas in output" rather than static values as the default function.

Phillipa

yes... corrected.

"Preserve formulas in output" should not be ticked when you add a new block. But existing blocks might already have it configured, either because it was previously configured, or because it was a pre-2.6 data source.