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 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()) 🪣 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 📝 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 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 is\ open && daysopen>90 stale open reqs !location missing location fieldcode1 >= 4 && (base / fieldcode2) < 0 90 high performer, underpaid 🔗 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)) 💡 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 &&