Calculations
Named formulas defined once on the model, so every dashboard reading it gets the same number.
A calculation is a formula with a name. Define Active customers once and every dashboard reading the model asks for it by name, rather than each one writing its own arithmetic and slowly drifting apart.
They live on the Calculations tab of a model.
Writing one
Name it
What people will call it. This is the name a dashboard asks for, so write it the way your team says it out loud.
Pick a folder
Optional, and only filing. Group by subject area once you have more than a screenful.
Write the formula
The editor lists every column in the model, grouped by table, so you can pick rather than remember. Click a table name to see its rows while you write.
Save
The formula is parsed before it is stored. A calculation that cannot be read is refused, with the position of the problem, rather than saved and left to fail later on a dashboard.
Which table a calculation belongs to is worked out from the columns it uses. There is nothing to pick.
The grammar
Formulas are aggregates over columns, combined with arithmetic.
Sum(revenue)
Sum(revenue) - Sum(cost)
Sum(revenue) / Count()
Round(Avg(response_time), 1)
Percentile(response_time, 95)
WeightedAvg(rating, responses)Aggregates
Sum Avg Min Max | The usual four. |
Count | Rows. Count() with no column counts every row. |
CountDistinct | Distinct values in a column. |
Median Mode Percentile | Distribution. |
StdDev Variance | Spread. |
First Last | Ends of the set. |
WeightedAvg | An average weighted by a second column. |
GroupBy | Aggregate per group. |
Round Abs | Wrap an expression rather than a column. |
Common spellings are accepted: Total for Sum, Average or Mean for Avg, Distinct or Unique for CountDistinct, Maximum and Minimum.
Filters
Add filter(...) as the last argument to narrow what an aggregate sees.
Sum(revenue, filter(status = "paid"))
Count(filter(status = "open" & priority = "high"))
Sum(amount, filter(region = "APAC" | region = "EMEA"))& is and, | is or. Conditions compare a column with =, !=, >, <, >=, <= against a number, a quoted string, or another column.
Reading another table
Name the table in square brackets:
Sum([Orders].amount) / CountDistinct([Customers].id)An unqualified column is read from the calculation's own table. As soon as a formula names a second table, qualify every column in it, including the ones inside a filter(). Flitch follows the model's joins to line the rows up, so the two tables must be joined for the calculation to resolve.
A wrong qualifier is worse than a missing one, because the arithmetic still works. CountDistinct([Customers].id) and CountDistinct(id) both return a number, and only one of them counts customers. Flitch raises an error rather than guessing when it cannot follow a qualifier, but the habit worth keeping is to say which table you mean.
Running one
The play button on a row, Run it, evaluates the calculation against your real rows and shows the number. It uses the same interpreter as a running dashboard, so what you see here is what a dashboard will show.
It is unavailable when the calculation reads a table the model has not joined to. There is no path between the rows, so there is no number to compute. Draw the join and it becomes available.
Missing joins
An amber mark beside a calculation's name means it reads a table the model has not joined to. Hover it and it names the join it wants. Nothing is auto-created: what a number means is not something to guess at, so it waits for you to draw the join or correct the formula.
Nothing writes a calculation without you. The Data assistant proposes them, and a proposal is not a definition until you apply it.
Importing from another model
Import calculations copies definitions from another model in the same space. Search across models, tick what you want, and it lands in this one as a copy. Editing it afterwards does not touch the original.
Useful when a second model covers the same subject area, and after duplicating a model to try something out.
Folders, search and sort
All three live on the list header. A folder is a label on the calculation, so moving one between folders changes nothing about what it computes. An empty folder can exist, so you can set up a structure before filling it.