Insights: How to Build QC Report for Cytology

Applies to: Looker Explores without LookML developer access · Cytology QC reporting

What this report does

This report shows, for each Cyto Tech, how many of their cases matched the final grade ("Concordant") and how many didn't ("Discordant"). The final grade comes from later in the review chain: Screener → Reviewer → Pathologist.

There are two ways to build it, since we don't have LookML access:

• Path A — Custom Measures (recommended). Faster and more reliable. Use this one first.
• Path B — Table Calculations (backup). Use this only if Path A isn't working for you.

Important: Don't mix the two paths. A Custom Measure can't use a Table Calculation field. Pick one and stick with it.

Step 1: Add the basic fields

In your Explore, add these fields in this order:

Run the report with just these fields first. Check that each case shows up once, with all three grades.

Path A: Custom Measures (start here)

Step 2: Create a "Final Grade" field

Go to Add → Custom Field → Custom Dimension. Use this formula:

if(${cytology_pathologist_results.pathologist_grade} != "", ${cytology_pathologist_results.pathologist_grade},
if(${cytology_screener_results.screener_grade} = ${cytology_reviewer_results.reviewer_grade}, ${cytology_reviewer_results.reviewer_grade},
${cytology_pathologist_results.pathologist_grade}))

Name it Final Grade.

What this does: if the Pathologist entered a grade, use that. If not, use the Reviewer's grade (but only when Screener and Reviewer agree). Otherwise, leave it blank.

Step 3: Create a "Concordant / Discordant" field

if(${cytology_screener_results.screener_grade} = ${final_grade}, "Concordant", "Discordant")

If Looker won't let this field point to Final Grade by name, just paste the full Final Grade formula in its place.

Step 4: Create three Custom Measures

For all three, set Field to measure to Case # of Cases. Do not use Count — it causes duplicate case counts.

Total Cases — leave the filter blank.

Concordant Count — filter:

${cytology_screener_results.screener_grade} = if(${cytology_pathologist_results.pathologist_grade} != "", ${cytology_pathologist_results.pathologist_grade}, ${cytology_reviewer_results.reviewer_grade})

Discordant Count — filter:

${cytology_screener_results.screener_grade} != if(${cytology_pathologist_results.pathologist_grade} != "", ${cytology_pathologist_results.pathologist_grade}, ${cytology_reviewer_results.reviewer_grade})

Step 5: Put the report together

Add these four fields to one Explore: Screened By Full Name, Total Cases, Concordant Count, Discordant Count.

Step 6: Group by Cyto Tech

Sort or subtotal by Screened By Full Name. Check your work: Concordant + Discordant should always add up to Total Cases for each person.

Path B: Table Calculations (backup option)

Use this only if Path A doesn't work. Every field below must be a Table Calculation — not a Custom Dimension, not a Custom Measure.

Step 1: Final Grade

if(${pathologist_grade} != "", ${pathologist_grade},
if(${screener_grade} = ${reviewer_grade}, ${reviewer_grade}, ${pathologist_grade}))

Step 2: Screener to Final Agreement

if(${screener_grade} = ${final_grade}, "Concordant", "Discordant")

Step 3: Concordant Count / Discordant Count

if(${screener_to_final_agreement} = "Concordant", 1, 0)
if(${screener_to_final_agreement} = "Discordant", 1, 0)

You can hide these two columns (click the eye icon) since they're just helper fields.

Step 4: Total Concordant / Total Discordant

Go to Add → Table Calculation. Set Calculation to Column Total. Point one at Concordant Count and the other at Discordant Count.

Step 5: Split by Cyto Tech

Pivot on Screened By Full Name. Each person's totals will now show separately.

Common mistakes to avoid

• Don't mix Table Calculations and Custom Measures — they can't reference each other.
• Use if(), not CASE WHEN — Looker's Custom Field editor doesn't support SQL syntax.
• Use != for "not equal," not <>.
• Always measure on Case # of Cases, not Count — otherwise case counts can double.
• If you get an "expression incomplete" error, check for smart/curly quotes. Retype them as straight quotes.

Looking ahead

If this report gets used regularly, it may be worth asking Tim or Diane (who have LookML access) to build Final Grade and the concordance logic as permanent fields. That would remove the risk of duplicate counts and skip the table-calc workaround altogether.