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 modelorsaved exploration. - Click the Measures dropdown - the
yorMeasuresdropdown 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 modelorsaved exploration. - Click the Measures dropdown - the
yorMeasuresdropdown 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
Saveto 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) operatorsceilandfloorrounds to the nearest integer greater or less than the input, respectivelyminandmaxreturn the minimum or maximum value, respectively, of an expressionabsreturns 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.