Plain-language manual. The whole thing is: update a few R parameters → run R → refresh Excel → copy values into program tabs. Everything below is detail on those four moves.
hsh_feed_Q#_FY##-##.csv. R never opens QuACK.Feed_Q3,
Feed_Q4, …). Formulas look values up from it.Key rule: formulas point at the table name
(Feed_Q#), not the sheet. Keep that name exact and
everything works.
=IF($AC3="","",IFERROR(IF(XLOOKUP($AC3&"|"&B$52,Feed_Q4[key],Feed_Q4[na_flag])="Y","NA",XLOOKUP($AC3&"|"&B$52,Feed_Q4[key],Feed_Q4[value])),""))=SORT(CHOOSECOLS(FILTER(Feed_Q4, Feed_Q4[Program Name]=$A$20, "No rows"), 10,6,7,8,11), 5)(Columns 10,6,7,8,11 = metric_label, numerator, denominator, value, sort_order; sorted by sort_order.)
NA = metric applies but couldn’t be
computed (no eligible clients). Distinct from 0%.value column is Decimal, not Whole
Number, or rates truncate to 0/1.Most of this ports over unchanged. Per new workbook:
The recurring per-quarter work never changes: 3–4 R parameters, run, refresh, copy.
Pathways Intake; Q4 will have two (one per Pathways
program). Revisit the R40 subsidy calc for Pathways 1 / Pathways 2 when
the two intakes are on the roster.subsidy_map uses
YAC Intake / Rising Up Intake /
SOAR Intake (from Section 0). Confirm each quarter if the
roster changes.You can handle program add/remove yourself (edit the program vector in that metric’s file). These two are trickier because they touch more than one place:
Contained to R — no crosswalk, no Excel changes.
Note: if the change alters the eligible cohort, an empty
cohort may now flip to NA (or back) — that’s intended
behavior, not a bug.
Touches R and the finalize crosswalk and the master tab. Do R first, then wire Excel.
In R: - [ ] Write the metric in the appropriate file
(or a new file sourced before finalize). It must append to
results_long with the standard columns:
metric_id, \Program Name`, numerator, denominator,
value. - [ ] Give it a uniquemetric_id. If a program with an empty/zero cohort should showNA(not vanish), emit every listed program with a zero-fillright_join`
(copy the pattern from an existing metric).
In hsh_finalize_feed.R: - [ ] Add the
new metric_id → row_id line to the
crosswalk (map it to the master row it belongs on;
program disambiguates, so several metric_ids can share one row_id). - [
] Add the row_id → label line to
row_labels (friendly name; it auto-gets sort_order from
the row number). - [ ] Run. Watch the console — a “missing from
crosswalk” warning means you emitted a metric_id you didn’t
map; a “duplicate keys” warning means two metrics
landed on the same row_id + program.
In Excel (that master tab): - [ ] Put the new
row_id in the helper column (AC) on the
metric’s master row. - [ ] Make sure that row has the fill formula
across its program columns (copy from an adjacent wired row). - [ ]
Refresh All → confirm it populates. - [ ] The By
Program and Raw Counts views need nothing —
they read all rows automatically.
If it’s a new master ROW (not just a new column of an
existing metric): - [ ] Insert the row on the master tab;
extend the helper column and formula onto it. - [ ] Row numbers shifted
— confirm the crosswalk row_ids still line up with the
master’s actual rows.
Different from “add a program to a metric” (below). A brand-new program has to be introduced in the data, the metric logic, and the QuACK columns.
In R: - [ ] Confirm the program appears in
active (and its assessment/service files) with a consistent
Program Name — check
active |> distinct(\Program
Name`). - [ ] Add it to the program vector of **every metric it reports on** (may be several files). - [ ] If it's an RRH/subsidy-type program, add it toplacement_lookup/subsidy_map/intake_programs`
as needed. - [ ] Run. The feed carries it automatically once metrics
emit it — no crosswalk change (crosswalk is per-metric, not
per-program).
In Excel (master tab) — a brand-new program is a new
COLUMN: - [ ] Add the program’s display header
in the column header row. - [ ] Add its row-52 helper
entry = the program’s exact feed Program Name. - [ ] Extend
the fill formula into that new column (copy an adjacent
program column across). - [ ] Refresh All → confirm the column
populates. - [ ] By Program and Raw Counts need nothing — they read all
programs automatically.
Note: removing a program entirely is the reverse — pull it from the metric vectors in R; the master column can be left or deleted.
The easy case: the program already lives in the data and the workbook, you’re just including it in one more metric’s list. - [ ] Add it to that metric’s program vector in R. Run → Refresh. Done. (Its master column already exists.)
Rule of thumb: - New metric = 3 places (metric file, crosswalk + labels, master helper/formula). - New program = R metric vectors (maybe several) + a new master COLUMN (header, row-52 name, formula). - Parameter change = 1 place (the metric’s call). - Existing program → one more metric = 1 place (that metric’s vector).
#REF!: the feed table got
deleted while formulas depended on it — Excel baked in
#REF!. Re-paste the formula (the table name resolves once
it exists again). Don’t delete a query a formula depends on; repoint its
Source instead.Program Name exactly. Fix the row-52 cell. (Watch:
Diamond → Diamond Youth Shelter,
CYHI → Castro Youth Housing Initiative,
Casa Ade. → Casa Adelante,
Routz (PSH) → Routz SH.)active
(e.g. Pathways I RRH vs Pathways 1). Run
active |> distinct(\Program Name`)` and match
exactly.NA program name:
the source file’s program column isn’t Program Name
(e.g. ProgramName). Rename at load in Section 0.By Program FILTER view instead (fully browser-safe), or
open the file via “Open in Desktop App” — but do NOT File → Download
(that forks a local copy). Opening the SharePoint file in desktop edits
it in place.=SORT(UNIQUE(Feed_Q#[Program Name]))), then point
the dropdown at that range (=$H$1# or a fixed range).period, key, row_id, metric_id, Program Name, numerator, denominator, value, na_flag, metric_label, sort_orderrow_id | Program Name
(e.g. R9|LEASE)=IF($AC3="","",IFERROR(IF(XLOOKUP($AC3&"|"&B$52,Feed_Q#[key],Feed_Q#[na_flag])="Y","NA",XLOOKUP($AC3&"|"&B$52,Feed_Q#[key],Feed_Q#[value])),""))=SORT(CHOOSECOLS(FILTER(Feed_Q#, Feed_Q#[Program Name]=$A$1, "No rows"), 10,6,7,8,11), 5)=IF($AC3="","",IFERROR(LET(k,$AC3&"|"&B$52,f,XLOOKUP(k,Feed_Q#[key],Feed_Q#[na_flag]),n,XLOOKUP(k,Feed_Q#[key],Feed_Q#[numerator]),d,XLOOKUP(k,Feed_Q#[key],Feed_Q#[denominator]),IF(f="Y","NA",IF(d>0,n&" / "&d,n))),""))active |> distinct(\Program
Name`)in R; theProgram Name` column in the feed CSV.