# Calculations

Hashboard supports building calculations based on your Measures. You can add calculations when you are exploring any data model or saved exploration directly from the Measures dropdown or Data tray.

Once you've added a calculation, it lives on the exploration even if inactive, i.e. not currently being used as a measure in the chart. Calculations can be edited at any time, and can be deleted from the Data tray when not being used as the only active measure.

### Quick vs. custom calculations

Hashboard separates calculations into two types: **quick calculations** and **custom calculations**.

By default, Hashboard provides a variety of **quick calculations**, such as rolling averages, rolling sums, differences,
percent changes, and more, which can be added an easy-to-use template interface.

More complex **custom calculations** can be added through our formula editor.
With custom calculations, you can nest formulas within each other and reference other measures and calculations.

If a breakout is applied, the calculation will be applied to each individual breakout series. If no breakout is applied, a second series will be added so you will see your measure side-by-side with the new calculation.

`Null`

values in the calculation will be treated as `0`

. In window functions
such as Rolling sum and Rolling average, they will not be included in the size
of the window.

## Add a quick calculation to a saved exploration

- Start exploring a
`data model`

or`saved exploration`

. - Click the Measures dropdown - the
`y`

or`Measures`

dropdown depending on the chart type. - Click
`+ Quick calculation`

. - A new menu will open allowing you to name your calculation and edit the calculation's options.

Quick calculations can be converted to custom calculations by clicking on the calculation in the Data tray
and selecting `Convert to custom calculation`

in the calculation's dropdown.

## Add a custom calculation to a saved exploration

- Start exploring a
`data model`

or`saved exploration`

. - Click the Measures dropdown - the
`y`

or`Measures`

dropdown depending on the chart type. - Click
`+ Custom calculation`

. - A new menu will open allowing you to set a unique name and alias for the calculation, and build the formula in our formula editor.
- When you have finished writing a valid formula, click
`Save`

to add the calculation to the chart.

### Building custom calculations

The formula editor supports all of the functions available as quick calculations, as well as the ability to reference other measures, calculations, and use arithmetic operators.

When referencing other measures and calculations, simply type the `alias`

of the measure or calculation you want to reference.
When you start typing, Hashboard will automatically suggest measures, calculations, and formulas that match your input.
To see the documentation of a particular measure, calculation, or formula, hover over it in the calculation editor to see more details.

For example, if you have a measure with alias `revenue`

and a measure with alias `cost`

,
you could create a custom calculation named `Profit`

with the formula `revenue - cost`

.

**Arithmetic functions**

There are various arithmetic functions available in the custom formula editor:

`+`

,`-`

,`*`

,`/`

,`^`

(exponentiation) and`%`

(modulus) operators`ceil`

and`floor`

rounds to the nearest integer greater or less than the input, respectively`min`

and`max`

return the minimum or maximum value, respectively, of an expression`abs`

returns the absolute value of the input

These functions can be used with other numbers, measures, calculations, or expressions to create richer and more complicated calculations.

## Calculation types

**Rolling average**

`rollingAverage(measure, period, anchor, minimumPeriod)`

Useful for helping smooth out noise in a dataset

Name | Type | Description |
---|---|---|

Measure | Hashboard measure alias | The alias of the Hashboard measure to use for the calculation, for example, `row_count` .Custom calculations have the ability to use the aliases of calculations as well in the place of a measure. |

Periods | Integer | The number of periods to include in the calculation (i.e. the size of the moving window). |

Anchor | "trailing" | "centered" | "leading" | Whether to set labels at the center of the window or at the edge. Values are `Trailing` , `Centered` , or `Leading` . For a timeseries chart, you will usually want this to be a `Trailing` average or sum. |

Minimum periods | String | The minimum number of observations required for the calculation to be defined for a certain point. For example if you take a seven day moving average, but there are only five days of data before this date, then the calculation will not be defined for that day. Defaults to the same as periods |

**Rolling sum**

`rollingSum(measure, period, anchor, minimumPeriod)`

For looking at the cumulative sum of a measure

Name | Type | Description |
---|---|---|

Measure | Hashboard measure alias | The alias of the Hashboard measure to use for the calculation, for example, `row_count` .Custom calculations have the ability to use the aliases of calculations as well in the place of a measure. |

Periods | Integer | The number of periods to include in the calculation (i.e. the size of the moving window). |

Anchor | "trailing" | "centered" | "leading" | Whether to set labels at the center of the window or at the edge. Values are `Trailing` , `Centered` , or `Leading` . For a timeseries chart, you will usually want this to be a `Trailing` average or sum. |

Minimum periods | String | The minimum number of observations required for the calculation to be defined for a certain point. For example if you take a seven day moving average, but there are only five days of data before this date, then the calculation will not be defined for that day. Defaults to the same as periods |

**Difference**

`difference(measure, offset)`

For evaluating the absolute change over time

Name | Type | Description |
---|---|---|

Measure | Hashboard measure alias | The alias of the Hashboard measure to use for the calculation, for example, `row_count` .Custom calculations have the ability to use the aliases of calculations as well in the place of a measure. |

Offset | Integer | The number of periods to look back for the comparison Offset for Difference is only configurable in custom calculations. Hashboard sets the offset to compare to the previous period by default in quick calculations. |

**Percent change**

`percentChange(measure, offset)`

For looking at the proportional change over time

Name | Type | Description |
---|---|---|

Measure | Hashboard measure alias | The alias of the Hashboard measure to use for the calculation, for example, `row_count` .Custom calculations have the ability to use the aliases of calculations as well in the place of a measure. |

Offset | Integer | The number of periods to look back for the comparison Offset for Percent Change is only configurable in custom calculations. Hashboard sets the offset to compare to the previous period by default in quick calculations. |

**Lag**

`lag(measure, offset)`

For looking the value shifted over time

Name | Type | Description |
---|---|---|

Measure | Hashboard measure alias | The alias of the Hashboard measure to use for the calculation, for example, `row_count` .Custom calculations have the ability to use the aliases of calculations as well in the place of a measure. |

Offset | Integer | The number of periods to look back for the comparison |

**Constant value**

*Constant value calculations are configurable via quick calculations. In custom calculations, use numbers as literals instead.*

Useful for fixed goals or other fixed values for comparison

Name | Type | Description |
---|---|---|

Value | Number | The numeric value to chart as a fixed line. |

**Percent of total**

`percentOfTotal(measure)`

For looking at the proportion of the total value over time

Name | Type | Description |
---|---|---|

Measure | Hashboard measure alias | The alias of the Hashboard measure to use for the calculation, for example, `row_count` .Custom calculations have the ability to use the aliases of calculations as well in the place of a measure. |

**First in sequence**

`first(measure)`

For looking at the value at the first point in time

Name | Type | Description |
---|---|---|

Measure | Hashboard measure alias | The alias of the Hashboard measure to use for the calculation, for example, `row_count` .Custom calculations have the ability to use the aliases of calculations as well in the place of a measure. |

**Last in sequence**

`last(measure)`

For looking at the value at the last point in time

Name | Type | Description |
---|---|---|

Measure | Hashboard measure alias | The alias of the Hashboard measure to use for the calculation, for example, `row_count` .Custom calculations have the ability to use the aliases of calculations as well in the place of a measure. |

## Advanced calculation options

For Table visualizatons, you can control several advanced options for calculation logic. These options are available in the control panel of the Data Explorer under the "Measures & Calculations" section.

### Window function options

These options control the behavior of calcuations that apply window functions to your post-aggregated datasets. These functions are:

- Rolling average
- Rolling sum
- Difference
- Percent change
- Lag
- First in sequence
- Last in sequence

Depending on the configuration below, these functions may partition or sort your data according to certain rules before being calculated.

### Window function partitioning

This option controls how values are grouped together when applying window-based calculations.

#### Partition by pivoted columns

When selected, window functions will apply separately to each column in your visualized table. Data is further partitioned by your Breakout, if one is applied.

Examples:

#### Partition by all additional attributes

When selected, window functions will apply separately for every unique combination of attribute values, excluding the first attribute in your table.

For instance, in the example below, separate rolling sums are calculated for:

- FIRSTORDER + Single Order + Gold member
- FIRSTORDER + Subscription + Gold member
- FREESHIP + Subscription + Gold member
- FREESHIP + Subscription + Gold member
- ...etc

### Window function partition sorting

This option controls how values are sorted within their partitions when applying window-based calculations.

#### Use ordering from underlying query

When selected, sorting within partitions will be maintained from how they are returned from your database. This means any sorting you've applied to your exploration will also apply to your window partitions.

#### Sort each partition by the first attribute

When selected, rows within each partition will be sorted by the first attribute, ascending, before window calculations are evaluated. This is most useful when your first attribute is a date. For instance, you may want to visually sort your table with the most recent dates first, but have a rolling sum that shows the running total starting at the earliest date.