Skip to main content

Status Collections and Formula Columns: Build Your Own Agent Metrics

Status Collections and Formula Columns: Build Your Own Agent Metrics

Group your Zendesk agent statuses into named sets, then write a spreadsheet formula over them — so a metric like "work time as a percentage of time on the clock, against our 85% target" is something you define yourself, in an afternoon, instead of something you ask us to build.

Why These Two Features Belong Together

Almost every productivity question a contact centre asks has the same shape: this group of statuses, as a share of that group of statuses. Answering it used to take two things Qvasa didn't have.

  • A way to name a group of statuses. Qvasa had exactly one opinion built in — the Productive mapping — and one is never enough. "On break" and "in a meeting" and "coaching" are all different things, and which of them counts as working is a decision only you can make.

  • A way to do arithmetic on the result. Two durations sitting in adjacent columns don't answer a question until you divide one by the other.

Status collections solve the first. Formula columns solve the second. Used together they turn "can you build us a Work Time metric?" into something you do yourself — and change without asking anyone.

This guide builds one worked example end to end: Work Time %, with an On track / Below target column beside it. Follow it with your own status names and you'll have your own metric at the end.

Before You Start

  • Status collections need your Zendesk unified agent statuses flowing into Qvasa. If you already use the Agent Statuses settings page to mark statuses as Productive, you're set. Anyone who can toggle Productive can manage collections; everyone else sees the page read-only and can still use collections in widgets and filters.

  • Formula columns are switched on per account. If you don't see Table Column Formulas in Settings, that's why — reach out to us and we'll turn it on.

  • Once formulas are on, two permissions control them. Manage your own table column formulas lets someone create formulas and edit or delete the ones they made; manage all table column formulas lets them edit or delete anyone's. Admins always have both. Separately, anyone who can edit a dashboard can add an existing formula to a table on it — building formulas and using them are deliberately different permissions.

  • Admins reach the page from Settings. Non-admins who hold either permission reach it from a card on User Settings.

Step 1: Create a Status Collection

Go to Settings and open Status Collections — it sits under Setup, just below Zendesk Agent Statuses. Click + Add status collection…, give it a name and a one-line description.

The name is used verbatim everywhere the collection appears, so "Available to Work" stays "Available to Work" on every widget built on it. The description becomes the tooltip wherever the collection is offered — worth writing, because the person building a dashboard six months from now needs to know what you meant.

For the worked example, create two collections:

  • Available to Work — every status where an agent can take work.

  • Paid Time Off the Floor — on the clock but not taking work: breaks, lunch, coaching.

A status can belong to as many collections as you like, and a collection can hold a single status if that's useful — a one-status collection's numbers match that status's own widgets exactly.

Step 2: Choose Its Statuses

Open the collection and use the search box at the bottom of the Statuses in this collection card. Search for a status and it's added immediately; the × on a row removes it. Statuses already in the collection aren't offered in the dropdown — remove one and it comes back. Membership saves as you go; the name and description have their own Save status collection button above.

Two things worth knowing here:

  • These are your real Zendesk statuses. Qvasa syncs them from Zendesk and doesn't create them. The PRODUCTIVE badge shows which ones your account already marks as productive, so you can see how your new collection relates to the built-in mapping.

  • Renaming a status in Zendesk won't break anything. A collection holds the statuses themselves and reads their names fresh every time, so a rename flows straight through.

Step 3: Offer the Collection as a Table Column

A collection starts life as something you can chart. To make it something a formula can read, it has to become a table column — and that's one button on the collection's own page.

On the Agents table columns card, click Offer as agents table columns. That puts "Collection name Total Time" in the agents table's column picker, and "Collection name Agents" and "Collection name Total Time" in the agent-field, skills and staffing-dimension tables' pickers.

This can't be undone. Once the columns exist, dashboards may be using them, so there's no switch to take them away again — deleting the collection is the only way. It's not a destructive step, but it is a one-way one, so offer the collections you actually intend to report on rather than all of them.

Do this for both Available to Work and Paid Time Off the Floor.

Step 4: Write the Formula

Open Table Column Formulas from Settings. Every formula on the account lives here, grouped by the table it belongs to, with its calculation in plain column names, its output format, its owner, and how many tables are currently using it.

Click + New formula. Give it a name, pick the table it belongs to (Agents, for this example), and choose how the result should read.

For Work Time %, set Show the result as to Percent with 1 decimal place, and build this calculation:

{{Available to Work Total Time}} / ({{Available to Work Total Time}} + {{Paid Time Off the Floor Total Time}})

Four things make this much easier than it looks:

  • Use Insert, don't type column names. The Insert a column panel on the left searches every column on the table — search "Available to Work", then click Insert to drop it in at your cursor. Behind the scenes Qvasa stores a stable reference rather than the name you see, which is exactly why a later rename can't break your formula. The formula box shows that stored reference; the Reads as line underneath shows the same formula back in plain column names, and that's the one to read while you work.

  • Durations are in seconds. A duration column reads as a number of seconds, so an hours figure is {{Some Total Time}} / 3600. For a percentage you don't need it — dividing one duration by another cancels the units out.

  • Percent means a fraction. With the format set to Percent, 0.25 displays as 25%. Divide; don't multiply by 100.

  • It validates as you type. ✓ This formula is valid appears under the box when it parses, the Columns in this formula chips confirm what you actually referenced, and Preview runs it against the first ten rows of real data with the arithmetic shown, so you can check it before saving.

Now add the target column. Create a second formula called Hits the 85% target, set Show the result as to Text, and write:

IF({{Work Time %}} >= 85%, "On track", "Below target")

Note what just happened: a formula can read another formula. You defined the percentage once, and the target check points at it — so when you revisit what counts as work time, you change one formula and both columns follow.

Step 5: Put Them on a Dashboard

On any dashboard with an agents table, click the + in the table header. That opens a searchable list of every column not yet on the table — your formulas appear there with an fx badge, and picking one offers to add the columns it reads at the same time. There's also a Create formula column… option, so you can write a new formula without leaving the dashboard.

That's the finished metric: two collection columns you defined, a percentage computed from them, and a plain-English verdict against your target — all sortable, all filterable by your usual agent filters.

Two details on the finished table:

  • Every cell shows its own working. Hover a formula value and the tooltip gives you the actual arithmetic for that row — the inputs, the operation and the result. When someone challenges an agent's number, the answer is on screen rather than in a spreadsheet somewhere. The column header's tooltip carries the description and the formula in readable names.

  • Formula columns are never lazy-loaded. Even if your account loads other columns on demand, a formula's inputs are fetched with the rest of the table in one pass, so the cells arrive already computed.

The Formula Language

It's a subset of what you already know from Excel and Google Sheets, with the same precedence rules.

  • References: {{column}}, for any column on the same table — including other formulas.

  • Values: numbers, "text", TRUE / FALSE, and percentages like 50%.

  • Operators: + - * / ^, & to join text, and the comparisons = <> < <= > >=.

  • Logic: IF, IFS, AND, OR, NOT

  • Errors: IFERROR, ISBLANK

  • Maths: ROUND, ROUNDUP, ROUNDDOWN, ABS, MIN, MAX, SUM, AVERAGE

  • Text: CONCATENATE

Output formats: number, percent, duration or text, with up to six decimal places. A duration result is formatted in hours and minutes for you.

Blanks and errors behave like a spreadsheet. A missing value counts as 0 in arithmetic and as empty in text. Errors — #DIV/0!, #VALUE!, #REF!, #N/A, #NUM! — travel through the formula until an IFERROR catches them, exactly as they would in Sheets.

A few examples to build from:

  • ROUND({{Available to Work Total Time}} / 3600, 1) — a duration as hours, to one decimal place.

  • IFS({{Work Time %}} >= 90%, "Excellent", {{Work Time %}} >= 85%, "On track", TRUE, "Below target") — banding, where TRUE is the catch-all.

  • IFERROR({{Break Total Time}} / {{Break Count}}, 0) — an average that shows 0 rather than an error for an agent who took no breaks.

  • IF(ISBLANK({{Lunch Total Time}}), "No lunch logged", "Yes") — distinguishing "no data" from "zero".

What Else Collections and Formulas Unlock

The table columns are the part that feeds formulas, but a collection is useful well beyond them. Once one exists you also get, on the widget picker:

  • Quick stats — agents in the collection right now, average agents in it over a period, agents in it who aren't occupied, how many times agents entered it, and total, average and median time in it.

  • Donuts — time in the collection broken down by agent, by Zendesk user field, by skill type, by all skills, and by Qvasa dimension.

  • Charts — agent count in the collection over time, plus by-agent-over-time variants.

  • A filter — Unified Agent Status Collection, to scope a widget or a whole dashboard to the agents currently in a named set. It combines with the per-status filter rather than replacing it.

And a formula isn't limited to tables either: any formula that produces a number, percentage or duration can be added as a standalone donut widget from the widget picker. Text formulas stay table-only — there's nothing to chart.

Formula columns work on far more than the agents table, too: organizations, groups, skills and a dozen more configurable tables each get their own formulas, scoped to that table's columns.

Troubleshooting

  • A cell reads #DIV/0! — the denominator was zero for that row. Usually it's an agent with no activity in the date range, which is correct behaviour rather than a fault. If you'd rather show something friendlier, wrap the whole thing: IFERROR(<your formula>, "No activity"). You can leave the format as Percent — rows with a real number still format as percentages, and the fallback shows as written.

  • A formula is flagged as broken, or a cell reads #REF! — one of the columns it references no longer exists on the account. The usual cause is a deleted collection or status. Open the formula and re-insert the column you meant.

  • Your collection isn't in the column picker — it hasn't been offered as a table column yet. Go back to Step 3.

  • You can't delete a formula — another formula reads it. Delete or repoint the formula that depends on it first. (The Used on column tells you how many tables are using each one.)

  • Numbers are far larger than expected — a duration column is in seconds. Divide by 3600 for hours, or set the output format to Duration and let Qvasa format it.

  • A percentage shows as 8500% — the Percent format already multiplies by 100. Remove the * 100 from your formula.

  • You can see the formulas page but the buttons are disabled — you hold "manage your own" and that formula belongs to someone else. Hovering the disabled button says so. Someone with "manage all", or an admin, can change it.

Did this answer your question?