Cheat Sheet

charthop cql (carrot) formula cheat sheet 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 🧩 where are you writing this? context syntax example smart calc / smart bucket plain expression base fieldcode1 data sheet calculated column plain expression diffyears(startdate, today()) form content block {{ }} {{formatmoney(base)}} form (live answer from same form) formanswers\['fieldcode'] formanswers\['fieldcode1'] 100 document / letter template {{ }} {{name}} markdown (profile tabs, home page) {{ }} values · {% %} logic see markdown section dashboard chart (advanced mode) {{ }} {{base / fieldcode1}} ⚡ operators symbol meaning example = exact match department func='engineering' != not equal status!='inactive' contains / fuzzy match title 'director' > < >= <= comparison base>100000 && or and and (interchangeable in most cases) department func='sales' && base>80000 || or department func='sales' || department func='marketing' ! not !department 'sales' fieldcode has any value fieldcode1 ? ternary — if/then/else base > 100000 ? 'high' 'low' ? elvis — value if exists, else fallback fieldcode1 ? 0 dateof fieldcode date the field was last written dateof base && vs and these work interchangeably in most filter and smart field contexts if a formula behaves unexpectedly, try switching to && 📐 performance & goals weight an individual goal's rating by its assigned weight fieldcode1 (fieldcode2 ? 0) 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 total weighted goal score round(fieldcode1 + fieldcode2 + fieldcode3, 2) 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 total performance rating (goals + values) round((fieldcode1 0 80) + (fieldcode2 0 20), 2) 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 average only the goals that were actually used (skip zeros and blanks) mean( fieldcode1 > 0 ? fieldcode1 null, fieldcode2 > 0 ? fieldcode2 null, fieldcode3 > 0 ? fieldcode3 null ) 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 combine multiple rating categories into one weighted score ((fieldcode1 ? 0) 0 50) +((fieldcode2 ? 0) 0 30) + ((fieldcode3 ? 0) 0 20) weights must sum to 1 0 ? 0 ensures a missing rating doesn't nullify the entire formula count how many times an employee hit a specific rating in recent cycles findhistoryvalues({fieldcode1}) reversed limit(6) count{it='meets expectations'} full history (no limit) findhistoryvalues({fieldcode1}) count{it='exceeds expectations'} 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 show a rating only if it was submitted before a review cutoff date {{dateof fieldcode1<='2024 11 10' ? fieldcode1 null}} use in a form content block repeat for each rating field prevents backdated or late entries from surfacing during calibration 💰 compensation compa ratio — where someone sits relative to their band midpoint base / fieldcode1 fieldcode1 = band midpoint result of 1 0 = at midpoint format as percent for display foundation for pay equity analysis, merit planning, and outlier flagging salary range penetration — how far through the band (0 = min, 1 = max) (base fieldcode1) / (fieldcode2 fieldcode1) fieldcode1 = band min · fieldcode2 = band max useful alongside compa ratio when bands are wide total cash compensation (base + target bonus) base + ((fieldcode1 ? 0) base) fieldcode1 = target bonus percentage field new base after a merit increase base + (base fieldcode1) fieldcode1 = merit percentage field best used as a data sheet calculated column during comp planning — no permanent field needed prorated salary for a mid year hire base (diffdays(startdate, '2026 01 01') / 365) replace '2026 01 01' with your fiscal year end date prorated annual cost with start and end date logic (full fiscal year) startdatejob > date('2026 01 01') ? (enddatejob ? monthlycost 12 (enddatejob startdatejob + 1) / 365 monthlycost 12 (date('2026 12 31') startdatejob + 1) / 365 ) (enddatejob && enddatejob < date('2026 12 31') ? monthlycost 12 (enddatejob date('2026 01 01') + 1) / 365 monthlycost 12 ) 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 prior salary — what someone earned before their last increase asofprimary(dateof base 1, {base}) dateof base = date of last base change subtracting 1 day retrieves the value just before it no custom fields needed year over year base salary change as a percentage (base asofprimary('2025 01 01', {base})) / asofprimary('2025 01 01', {base}) as a dollar amount base asofprimary('2025 01 01', {base}) replace '2025 01 01' with your baseline date use asofprimary — not asof — to exclude draft scenario data total comp including equity vesting cashcomp + vestvalue(today(), nextanniversary(startdate)) built in fields only adds total cash to the value of equity vesting in the next 12 months 📅 tenure, dates & eligibility question formula how long has someone been here? diffyears(startdate, today()) how long in their current role? diffdays(titledate, today()) when is their next work anniversary? nextanniversary(startdate) how old is this employee? diffyears(birthdate, today()) did their manager change recently? diffdays(dateof manager, today()) <= 90 when were they last eligible for a raise? dateof base when will they next be eligible? dateof base + 365 how long has this req been open? diffdays(opendate, today()) when did this person become a manager? dateof directreports did they become a manager in the last 30 days? diffdays(dateof directreports, today()) <= 30 🪣 smart bucket templates 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 performance tiers · fieldcode1 = weighted score field label expression high performer fieldcode1 >= 4 5 strong performer fieldcode1 >= 3 5 && fieldcode1 < 4 5 meets expectations fieldcode1 >= 2 5 && fieldcode1 < 3 5 needs improvement fieldcode1 < 2 5 compa ratio bands · fieldcode1 = compa ratio smart calc field label expression below band fieldcode1 < 0 80 in range fieldcode1 >= 0 80 && fieldcode1 <= 1 20 above band fieldcode1 > 1 20 tenure bands · uses built in startdate label expression new hire (<1 yr) diffyears(startdate, today()) < 1 early career (1–3 yrs) diffyears(startdate, today()) >= 1 && diffyears(startdate, today()) < 3 established (3–5 yrs) diffyears(startdate, today()) >= 3 && diffyears(startdate, today()) < 5 veteran (5+ yrs) diffyears(startdate, today()) >= 5 merit eligibility · fieldcode1 = performance rating field label expression eligible diffmonths(startdate, today()) >= 6 && fieldcode1 >= 3 ineligible – too new diffmonths(startdate, today()) < 6 ineligible – rating fieldcode1 < 3 flight risk · fieldcode1 = band minimum field label expression high risk diffmonths(titledate, today()) >= 24 && base < fieldcode1 medium risk diffmonths(titledate, today()) >= 18 || base < fieldcode1 low risk diffmonths(titledate, today()) < 18 && base >= fieldcode1 span of control · uses built in directreports · apply to jobs label expression under leveraged (1–3) length(directreports) >= 1 && length(directreports) <= 3 healthy (4–8) length(directreports) >= 4 && length(directreports) <= 8 over leveraged (9+) length(directreports) >= 9 open req age · uses built in opendate label expression fresh (<30 days) diffdays(opendate, today()) < 30 active (30–60 days) diffdays(opendate, today()) >= 30 && diffdays(opendate, today()) < 60 aging (60–90 days) diffdays(opendate, today()) >= 60 && diffdays(opendate, today()) < 90 stale (90+ days) diffdays(opendate, today()) >= 90 📋 forms embedding live cql data how to add a cql content block to a form go to people ops tools → forms → open or create a form click + add question / block → select content block type type your text and embed cql using {{expression}} syntax save and preview — expressions render live against the employee the form is about 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 employee context card — put this at the top of any review or comp form name {{name}} | title {{title}} | dept {{department name}}tenure {{formatround(diffyears(startdate, today()), 1)}} years current base {{formatmoney(base)}} band {{formatmoney(fieldcode1)}} – {{formatmoney(fieldcode2)}} compa ratio {{formatround(base / fieldcode3, 2)}} last rating {{asofprimary('2024 07 01', {fieldcode4})}} replace fieldcode1–4 with your band min, band max, midpoint, and rating fields compensation flag — auto surface outliers to the reviewer {{(base / fieldcode1) < 0 80 ? '⚠️ below band minimum — flag for discussion ' '✅ within compensation band '}} prior vs current salary prior base {{formatmoney(asofprimary(dateof base 1, {base}))}}current base {{formatmoney(base)}} yoy salary change yoy base change {{formatpercent((base asofprimary('2025 01 01', {base})) / asofprimary('2025 01 01', {base}))}} total weightage validation — confirm goal weights sum to 100% total weightage {{(fieldcode1 100)+(fieldcode2 100)+(fieldcode3 100)}}% extend with +(fieldcoden 100) for each additional goal weightage field list a manager's direct reports inline — useful for manager attestation forms {% assign directs = db job find{it manager=person && is\ person} %}{% for direct in directs %} {{direct name}} — {{direct title}} {% endfor %} great for single manager attestation forms where you want the reviewer to see all their reports in one place without submitting separately per person 📝 markdown conditional display use in profile tabs, the home page, and document templates {{ }} renders a value {% %} controls show/hide logic show a block only if a condition is true {% if fieldcode1 >= 4 %}⭐ high performer — eligible for promotion discussion {% endif %} show different content based on a condition {% if diffyears(startdate, today()) >= 1 %}eligible for annual review {% else %} not yet eligible — less than 1 year of tenure {% endif %} show content only if a field was set on or before a specific date {% if ((fieldcode1 && dateof fieldcode1='2024 11 15') ? asofprimary('2024 11 15', {fieldcode1}) null) %}rating (as of nov 15) {{asofprimary('2024 11 15', {fieldcode1})}} {% endif %} fieldcode1 confirms a value exists · dateof fieldcode1 confirms when it was written · asofprimary retrieves the locked value nested conditions {% if department func='engineering' %} {% if fieldcode1 >= 4 %} high performing engineer — flag for promotion discussion {% endif %} {% endif %} 🔍 common filter queries expression what it returns is\ active all active employees is\ manager people managers only !is\ manager individual contributors only department func='engineering' specific department !department 'sales' exclude a department startdate<'2022 01 01' started before a date !fieldcode1 missing a specific field value fieldcode1>=4 field at or above a threshold (base / fieldcode1) < 0 90 below 90% compa ratio diffyears(startdate, today()) >= 3 3+ years tenure diffmonths(titledate, today()) >= 18 no title change in 18+ months diffdays(dateof manager, today()) <= 90 manager changed in last 90 days diffdays(dateof directreports, today()) <= 30 became a manager in last 30 days is\ open && daysopen>90 stale open reqs !location missing location fieldcode1 >= 4 && (base / fieldcode2) < 0 90 high performer, underpaid anniversary=today work anniversary is today enddateorg=today+11 departing in exactly 11 days ⚙️ actions & approval chains 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 trigger an action on a specific date relative to end date enddateorg=today+11 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 trigger an action on work anniversary anniversary=today 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 filter action audience to people who have not yet completed a form findformtasksbyassessment('review name', 'form name') filter{with(person,jobfilter) && status\ pending} count() > 0 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 conditional approval stage expressions 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 trigger only if a specific field changed change before base != change after base combine multiple fields with || (change before base != change after base) || (change before fieldcode1 != change after fieldcode1) trigger only if manager changed change before manager != change after manager trigger based on total cost impact across all changes in a scenario scenariochanges sum{change cost} > 500000 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 trigger if any change in the scenario affects a specific department scenariochanges any{department='engineering'} 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 trigger only if every change in the scenario is in a specific department scenariochanges all{department='engineering'} stricter than any{} — the stage only fires if the entire scenario batch is within that department skip it if the batch is mixed trigger based on both scenario content and who is submitting scenariochanges all{department='engineering'} && title='ceo' 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 trigger only when a specific person submits a scenario name 'first last' 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 📊 reporting & survey queries these functions are used in dashboard charts (advanced mode) to report on form completion, survey responses, and headcount metrics form completion rate for a review cycle findformtasksbyassessment('review name', 'form name') countpercent{status\ done} 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 count responses above a threshold for a specific question findanswers('fieldcode') count{value>=4} use for single metric charts on engagement or pulse surveys fieldcode is the field code of the question, not the form name count responses to a form question filtered by org findresponsesbyassessment('assessment name', 'survey name') filter{with(submitperson,jobfilter)} count() adding filter{with(submitperson,jobfilter)} applies your current org chart filter so the chart respects department, location, or other slices survey completion rate with org filter findformtasksbyassessment('assessment name', 'form name') filter{with(person,jobfilter)} countpercent{status\ done} rollup a field value across a manager's entire team — apply to manager jobs cost + underjobs sum{it cost} average a field across the team underjobs mean{it fieldcode1} 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 multi level manager chain — display reporting levels as separate fields level 3 (manager's manager's manager) manager manager manager level 4 manager manager manager manager 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 count number of manager changes during tenure findhistoryvalues({manager}) count() 1 subtracts 1 to exclude the original hire assignment counts across all jobs the person has held at the org 🔗 person field resolution assign a linked person (e g , talent partner, buddy) based on conditions step 1 — smart bucket each condition outputs a personid value step 2 — smart calc expression is just the bucket field code smartbucketfieldcode step 3 — set expected return type to person in the smart calc settings 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 🕰️ asof vs asofprimary asof asofprimary includes scenario/draft data ✅ ❌ use for what if / scenario planning baselines, historical lookups, audit safe reporting asofprimary('2025 01 01', {base}) base salary on jan 1 (primary data only)asofprimary('2024 07 01', {fieldcode1}) rating confirmed as of july 1 🧮 utility functions function what it does example round(x, 2) round to decimal places round(fieldcode1, 2) formatround(x, 1) round + format as string formatround(fieldcode1, 1) formatmoney(x) format as currency formatmoney(base) formatpercent(x) format as percent formatpercent(fieldcode1) formatdate(d, 'pattern') format date as string formatdate(startdate, 'mmmm d, yyyy') abs(x) absolute value abs(base fieldcode1) max(a, b) larger of two values max(base, fieldcode1) min(a, b) smaller of two values min(base, fieldcode1) mean(a, b, c) average, excluding nulls mean(fieldcode1, fieldcode2, fieldcode3) length(list) count items in list length(directreports) diffyears(d1, d2) years between dates diffyears(startdate, today()) diffmonths(d1, d2) months between dates diffmonths(startdate, today()) diffdays(d1, d2) days between dates diffdays(opendate, today()) nextanniversary(d) next anniversary date nextanniversary(startdate) asof(date, {expr}) value on date (incl scenario) asof('2024 07 01', {fieldcode1}) asofprimary(date, {expr}) value on date (primary only) asofprimary('2025 01 01', {base}) findhistoryvalues({field}) all historical values as list findhistoryvalues({fieldcode1}) vestvalue(d1, d2) equity vesting value in window vestvalue(today(), nextanniversary(startdate)) distance(addr1, addr2, unit) distance between two addresses distance(address, location address, 'miles') db job find{condition} query jobs across the org db job find{it startdate >= today} 💡 quick reference common mistakes ❌ mistake ✅ fix field is blank and breaks formula add ? 0 — e g , fieldcode1 ? 0 mean() averaging zeros as if they're real scores use fieldcode1 > 0 ? fieldcode1 null using field code inside count{} use it — e g , count{it='value'} asof pulling in draft scenario data switch to asofprimary for baselines person field returning blank check smart bucket outputs personid, not email formula breaks after renaming a field field codes don't auto update — fix references manually and not working in a specific context switch to && findhistoryvalues returning oldest values first add reversed before limit() dashboard query breaks when form name has a colon or trailing space use the form/assessment id instead of the name scheduled action fires every day instead of once use = not <= for date based action filters approval chain stage fires even when condition isn't met test the expression as a filter on the scenario changes tab first findformtasksbyassessment chart breaks when adding a filter use filter{with(person,jobfilter)} — not with(submitperson,jobfilter) for task based queries