Calculations
There are several places to calculate, and the right one depends on whether you need a new column or a summary of existing rows.
See them working together
This dashboard uses all of them. A Formula block calculates Revenue for each row, using a Discount parameter. The chart sums Revenue per region, and the KPI sums it overall. Move the slider and they all change.
A formula, a parameter, a chart and a KPI working together
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
Which calculation do I need?
| You want | Use |
|---|---|
| A new column per row, such as quantity times price | Formula in Strata |
| A total or average per category in a chart | Operation on the chart |
| One headline number | Operation on a Number, Progress, Gauge or Status |
| A number the reader can change | Parameter read by a Formula |
| A live number inside a sentence | Values on Text |
Per row: Formula in Strata
Formula adds or replaces columns, row by row, in your flow. Every visual using the Vista Source downstream can then use the new column like any other.
- Add the Formula block in Strata, before the Vista Source. Or add one from Vista, as below.
- Write a SQL expression with column names, for example
Quantity * Price. Put a column name that contains spaces in double quotes, such as"Unit Price".
To add one without leaving Vista: select a visual and select Change next to Data source, or open the Sources tab, then expand Calculated fields and select Add calculation. Fill in the column and expression and select Create calculation. This places a Formula block in your flow. Use Reveal in Strata to see it. It isn't a separate kind of calculation.
Per group: chart operations
Charts group rows, then calculate one value per group. Choose it with Operation next to the value field in the Data tab. The result isn't added to your data.
Operations include Sum, Average, Median, Minimum, Maximum, percentiles, First by time, Last by time, Count values, Count distinct, Count rows, Count true and Percentage true. Text columns can only be counted. Numeric operations ignore missing values.
One value: KPI calculations
KPI visuals calculate one value from all rows they receive, using Value and Operation. They can also compare it: use Comparison on a Number visual, or Target on Progress and Gauge. Goal sets what counts as good: Higher is better or Lower is better. Number, Gauge and Status also offer Within a range.
Changeable: parameters
A parameter gives a formula a value the reader can change. A Formula expression reads it as params.name. In the example above, the expression multiplies by (1 - params.discount / 100.0). See Parameters and filters to create one.
Calculations follow the preview sample
Sums and counts are the most visibly affected. See previews use a sample.
Next
Every Formula setting and function.
Calculations FAQs
- Where do I put a calculation that several visuals need?
- In a Formula block in Strata, before the Vista Source. Every visual that uses the source can then use the column.
- Why is my total smaller in the editor?
- The editor calculates from a sample of each source, 100 rows by default. A published dashboard uses all rows.
Chart your data. Build it in Vista.
Vista opens in your browser, with no account.