CQL (Carrot Query Language) is ChartHop's built-in expression language for searching, filtering, calculating, and displaying people data. The same logic works everywhere — only the syntax wrapper changes.

&& vs and: These work interchangeably in most filter and Smart Field contexts. If a formula behaves unexpectedly, try switching to &&.

fieldCode1 = goal rating · fieldCode2 = goal weightage · ?: 0 prevents a blank weight from breaking the formula. Create one Smart Calc per goal, then sum them for the total.

Each fieldCode = a per-goal weighted rating Smart Calc field. Add a term for each goal your org uses. Keeping this as its own field makes it easy to reference in forms and the final rating calc.

fieldCode1 = total weighted goal score · fieldCode2 = values/brand score. Adjust the 0.80/0.20 split to match your org's weighting — multipliers must sum to 1.0.

Extend with fieldCodeN > 0 ? fieldCodeN : null for each additional goal. mean() skips nulls but not zeros — this pattern ensures unused goals don't drag the average down.

Weights must sum to 1.0. ?: 0 ensures a missing rating doesn't nullify the entire formula.

.reversed = most recent first · .limit(N) = last N cycles · Always use it inside .count{} — not the original field code. Gives reviewers a consistency signal, not just the most recent result.

Use in a form content block. Repeat for each rating field. Prevents backdated or late entries from surfacing during calibration.

fieldCode1 = band midpoint. Result of 1.0 = at midpoint. Format as percent for display. Foundation for pay equity analysis, merit planning, and outlier flagging.

fieldCode1 = band min · fieldCode2 = band max. Useful alongside compa-ratio when bands are wide.

fieldCode1 = target bonus percentage field.

fieldCode1 = merit percentage field. Best used as a Data Sheet calculated column during comp planning — no permanent field needed.

Replace '2026-01-01' with your fiscal year-end date.

Use this for scenario planning and headcount cost forecasting. Handles mid-year starts, mid-year terms, and full-year employees in one expression. To show proration as a % of annual cost: proratedFyCost / (monthlyCost * 12) * 100. Update the year dates each cycle.

dateOf.base = date of last base change. Subtracting 1 day retrieves the value just before it. No custom fields needed.

Replace '2025-01-01' with your baseline date. Use asOfPrimary — not asOf — to exclude draft scenario data.

Built-in fields only. Adds total cash to the value of equity vesting in the next 12 months.

Smart Buckets assign a color-coded label based on CQL conditions. Use them on the org chart, Data Sheet, and dashboards for instant visual grouping.

Content blocks are read-only. They display data but cannot be edited by the reviewer. Use formAnswers['fieldCode'] (instead of just fieldCode) when reading a value entered earlier in the same form session.

Replace fieldCode1–4 with your band min, band max, midpoint, and rating fields.

Extend with +(fieldCodeN*100) for each additional goal weightage field.

Great for single manager attestation forms where you want the reviewer to see all their reports in one place without submitting separately per person.

Use in profile tabs, the home page, and document templates. {{ }} renders a value. {% %} controls show/hide logic.

fieldCode1:* confirms a value exists · dateOf.fieldCode1 confirms when it was written · asOfPrimary retrieves the locked value.

Carrot powers both the filters that determine who receives an action and the conditional logic that controls whether an approval stage fires. These are some of the most common patterns.

Use = not <= for scheduled actions. Using <= causes the action to fire every day until the date arrives. Replace 11 with however many days of lead time you need.

For orgs that use a custom hire date field instead of the built-in startDate, create a Smart Calc using nextAnniversary(customHireDateField) and filter on that field equaling today instead.

Use this as a scheduled action filter to send reminders only to people who still have outstanding tasks. Swap status:pending for status:done to target completers instead.

The expressions below go in the "Only include stage if" field on an approval stage. They control whether a given stage fires at all. Always test your expression as a filter on the scenario Changes tab first — if it returns results there, it will fire in the approval chain.

Combine multiple fields with ||: (change.before.base != change.after.base) || (change.before.fieldCode1 != change.after.fieldCode1)

Use when you want approval based on the combined salary impact of all changes, not just a single job. For example: require VP approval when the total cost of a scenario exceeds $500,000.

Use when at least one change in the batch needs to match — even if others don't. Good for routing Engineering Director approval whenever any Engineering job is touched.

Stricter than .any{} — the stage only fires if the entire scenario batch is within that department. Skip it if the batch is mixed.

Combines a condition about the changes with a condition about the submitter. In this example: only include this stage when all changes are in Engineering AND the person submitting is the CEO. Mix and match to build precise routing logic.

Set the condition type to Custom and use name: with the person's name. Confirmed working pattern for conditional routing based on the submitter's identity.

These functions are used in dashboard charts (Advanced mode) to report on form completion, survey responses, and headcount metrics.

One of the most common dashboard queries for performance and engagement reporting. If the form name contains a colon (:) or has a trailing space, use the form and assessment IDs instead of names — special characters in names can break the query.

Use for single-metric charts on engagement or pulse surveys. fieldCode is the field code of the question, not the form name.

Adding .filter{with(submitPerson,jobFilter)} applies your current org chart filter so the chart respects department, location, or other slices.

underJobs traverses the full org tree below a person, not just direct reports. Great for manager scorecards, team cost rollups, and org chart visualizations. Use directJobs instead if you only want one level down.

Create one Smart Calc field per level. Built-in fields cover levels 1 and 2. Many employees at higher levels will return blank — that's expected. Use ?: '' to suppress null display if needed.

Subtracts 1 to exclude the original hire assignment. Counts across all jobs the person has held at the org.

Use personId — not email. Email does not reliably resolve to the Person type in ChartHop. To find a personId: use the ChartHop API, or reference manager.id as a pattern example.