OneAnalytics docs

Formulas — the measure DSL

OneAnalytics measures live in the semantic model or in a report's Formula panel. The DSL is intentionally small — 20 functions cover 95 % of BI needs, and it compiles to dialect-specific SQL.

Basic aggregates

revenue        = sum([amount])
order_count    = count([id])
unique_buyers  = count_distinct([customer_id])
avg_order_value= avg([amount])
min_order      = min([amount])
max_order      = max([amount])

Square brackets reference columns from the dataset. Bare identifiers reference other measures.

Ratios

margin_pct = gross_profit / revenue

Division is ratio-safe — 0 / 0 becomes null, not an error. Format with format: percent in the measure definition.

Filtered (CALCULATE-style)

revenue_north = filter(revenue, [region] = "North")
revenue_ytd   = filter(revenue, [date] >= date_trunc("year", today()))

filter(measure, predicate) re-scopes measure under the predicate. Multiple predicates AND together: filter(revenue, [region] = "North" and [channel] = "retail").

Time intelligence

revenue_prev_year = same_period_prev_year(revenue, [date])
revenue_prev_mtd  = same_period_mtd(revenue, [date])
rev_yoy_growth    = (revenue / revenue_prev_year) - 1
rolling_7d        = rolling(revenue, [date], 7, "day")
running_total     = running_sum(revenue, [date])

Time functions auto-detect the grain from the active report context (day, week, month, quarter, year). Override with the grain argument: rolling(revenue, [date], 7, "day").

Conditional

status = if([amount] > 100000, "big", if([amount] > 10000, "medium", "small"))
tier   = switch([segment],
               "enterprise", "T1",
               "midmarket",  "T2",
               "smb",        "T3",
               "Other")

if, switch, coalesce, is_null, is_blank — all map to standard SQL.

Errors

Compile errors show inline as you type, with the line/column of the problem. Common ones:

  • Unknown identifier foo — column not in the dataset, or measure not yet saved.
  • Type mismatch — adding a string to a number; use to_number(...).
  • Circular reference — measure A depends on B depends on A.

Testing

Click Evaluate in the Formula panel to see the compiled SQL and a 10-row sample. For full-dataset validation, add the measure to a visual and check the numbers.