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? true 213,213,213left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type ⚡ operators true 213,213,213left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type && 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 true 319,320left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 🪣 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 true 319,320left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type compa ratio bands · fieldcode1 = compa ratio smart calc field true 319,320left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type tenure bands · uses built in startdate true 319,320left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type merit eligibility · fieldcode1 = performance rating field true 319,320left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type flight risk · fieldcode1 = band minimum field true 319,320left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type span of control · uses built in directreports · apply to jobs true 319,320left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type open req age · uses built in opendate true 319,320left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 📋 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 true 213,213,213left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 true 213,213,213left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 💡 quick reference common mistakes true 319,320left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type