Count a row only when its group qualifies

Count each document's WIP output, but only for documents that consumed material. Excel does it with a helper column and SUMIFS on A2. Here is the same thing in Xeplr Analytics, why the first formula everyone writes is refused, the fix that looks right and gives 0 everywhere, and the one that works, in one fast database pass.

The question

For each production order: how much WIP output did it produce, in thousands? Count an order’s output only if that order also consumed material (raw material, by-product or scrap).

The rows are an item ledger, as in Business Central or any ERP: one row per movement, with these columns.

The question mixes two levels:

• Per row: “is this row WIP output, with a quantity above 0?”
• Per order: “did this row’s order consume anything?” That is a total over the order’s other rows.

The sample data

Eleven rows, four orders. Each order is there to test one thing.

The answer to check against is at the end, in Check your result.

How you would do it in Excel

1. Add a helper column next to the data, on every row:

   =IF(AND(SUM(SUMIFS(D:D, A:A, A2, B:B, "Consumption", C:C, {"RM","BYPROD","SCRAP","SCRAPS"})) > 0,
           B2 = "Output", C2 = "WIP", D2 > 0),
       D2 / 1000, 0)

   (SUM(…) around SUMIFS adds up the four posting groups, one SUMIFS result each.)

2. Make a pivot table: Document_No in rows, Sum of the helper column.

The important part is A:A, A2 inside SUMIFS: add up only the rows whose order is the same as this row’s order. Without it, SUMIFS adds up consumption across the whole sheet, every order together, and the check means something else.

The first formula everyone writes, and why it is refused

if(sumif(Entry_Type = "Consumption" and Inventory_Posting_Group in ("RM", "BYPROD", "SCRAP", "SCRAPS"), Quantity) > 0
   and Entry_Type = "Output" and Inventory_Posting_Group = "WIP" and Quantity > 0,
   Quantity / 1000, 0)

It reads naturally, but the sumif does not say whose rows it adds up. That is the A:A, A2 part missing. Analytics refuses it before it reaches the database:

Entry_Type is a value of each row, but sumif() / countif() is one value for a whole group — the database cannot use both at once. Say whose rows the total covers, as Excel’s SUMIFS(…, Doc, A2) does: sumif(Document_No = $thisrow.Document_No and …, Quantity) — then Entry_Type is fine beside it. Or put Entry_Type inside a total too: sumif(… and Entry_Type …, …)

A formula saved before 17 October 2026 is plain SQL and goes straight to the database, which says instead: column “Entry_Type” must appear in the GROUP BY clause or be used in an aggregate function.

Why the database cannot run it

A database works out a summary in stages:

1. It reads the rows. Each has its own Entry_Type and Quantity.
2. It groups them, one group per order.
3. It works out each group’s totals (SUM, COUNT).
4. It hands back one row per group. The individual rows are gone.

The formula needs a total (the order’s consumption) and a row’s own value (this row’s Entry_Type) at the same time. Before step 3 there is no total; after it there are no rows. So the database asks for Entry_Type to become part of the group, the only way it can still be there after grouping.

The fix that looks right, and gives 0 everywhere

Following the database’s advice means grouping by Document_No, Entry_Type and Inventory_Posting_Group. That doesn’t add a detail. It changes what a group is: each order is split into one group per entry type and posting group, and every total inside the formula only sees its own small group.

Order D1 becomes four groups:

The WIP output now sits in a group with no consumption, so its check fails. Run on the sample, every one of the 10 groups gives 0, and you get several rows per order instead of one.

The rule: never add a column to the grouping just because a formula reads it. Grouping decides which rows each total adds up. A formula can read a column that is not grouped and not even shown.

The fix: say whose rows the total covers

Add the A2 part. In a formula it is $thisrow: this row’s value of a column.

if(sumif(Document_No = $thisrow.Document_No
         and Entry_Type = "Consumption"
         and Inventory_Posting_Group in ("RM", "BYPROD", "SCRAP", "SCRAPS"), Quantity) > 0
   and Entry_Type = "Output"
   and Inventory_Posting_Group = "WIP"
   and Quantity > 0,
   Quantity / 1000, 0)

Read it as:

Now the total is one value per row: its order’s consumption. So the formula is a row formula, exactly like the Excel helper column, and the row’s own Entry_Type and Quantity can sit beside it.

Write text in straight quotes, double or single: "Output" or 'Output'. The curly quotes ‘ ’ and “ ” that come from copying out of Word or a chat window are not accepted: Unexpected character “‘”. Capitals don’t matter: "Output" also matches OUTPUT and output.

Build it, step by step

1. In the left menu, Datasets → + New dataset, on the connection that holds the ledger. Click the ledger table in the Tables list to place it (03.2).
2. On the table card, tick only Document_No. Untick Entry_Type, Inventory_Posting_Group and Quantity. The formula reads them anyway; ticked, they would become groups (see above).
3. + Add formula column → Custom formula, and paste the formula into Expression.
4. Under the box, check it says Row formula and “One value per row, using the total of the rows that share this row’s values ($thisrow)…”. If it shows an error, see When it says no.
5. Alias: Output_k. Data type: number, so it can be summed.
6. Aggregate: SUM. That is the pivot table’s Sum of the helper column: each order adds up its rows.
7. Add column, then Run: one row per order.

You can now build reports and widgets on this dataset like any other. Output_k is an ordinary number column.

Check your result

These are the figures Analytics gives on this sample, on Postgres and DuckDB. The product’s own test builds this exact dataset and checks these figures.

What runs in the database

You don’t have to write SQL. This is what Analytics sends, slightly simplified: the real statement also ignores capitals and treats blanks as 0.

SELECT
  p."Document_No",
  CASE WHEN SUM(CASE WHEN p."Entry_Type" = 'Consumption'
                      AND p."Inventory_Posting_Group" IN ('RM', 'BYPROD', 'SCRAP', 'SCRAPS')
                     THEN p."Quantity" ELSE 0 END) > 0
       THEN SUM(CASE WHEN p."Entry_Type" = 'Output'
                      AND p."Inventory_Posting_Group" = 'WIP'
                      AND p."Quantity" > 0
                     THEN p."Quantity" / 1000 ELSE 0 END)
       ELSE 0 END AS "Output_k"
FROM item_ledger p
GROUP BY p."Document_No"

• One read of the table, one plain GROUP BY. No window function, no sort, no subquery: as fast as any summary.
• Only Document_No is grouped. Entry_Type and Quantity are used inside SUM(…), which is all the database needs.
• It works because “did this order consume?” is the same for every row of the order, so Analytics moves it outside the sum. Your formula stays written the way you think: per row.

Preview SQL on the dataset shows the full statement.

Variations

Consumption is stored as negative

In many ERPs consumption is a negative quantity. Then “the order consumed” means a total below 0: change > 0 to < 0. If every order comes out 0, check this first.

Per order and month: two questions, two formulas

Tick a Month and a Year column as well (often formula columns, e.g. MONTHNAME and YEAR of the posting date) to get one row per order and month. Then the formula decides what “the order consumed” means:

• Include Year: Month = $thisrow.Month alone puts January 2025 and January 2026 together.
• Name Month and Year as their table cards do. A formula column works like any column, and you don’t repeat its formula: Analytics puts Month’s own expression in, the same one it groups by. Capitals don’t matter: month finds Month.
• An order that consumes in January and produces in February shows its February output with the first formula, and 0 with the second. Pick the question you mean.
• Both still run in one pass over the table. The first adds up each order’s monthly totals across its months, over the few grouped rows only, never the whole ledger.

The same goes for any column you tick, such as Item_No or Location_Code: leave it out of the $thisrow part for “the whole order”, or add Item_No = $thisrow.Item_No for “this order and item”.

One value per row, not per order

Leave Aggregate on None on every column, so the dataset doesn’t group, and tick the columns you want to see. Each ledger row keeps its own value: its output in thousands if its order consumed, else 0. Analytics then uses a window function (SUM(…) OVER (PARTITION BY Document_No)), which is fine for a per-row list.

In a report instead of a dataset

The same formula works as a report SQL formula (04.7). There, the order’s total covers only the rows the report’s filters keep: filter the report to one month, and “did this order consume” means in that month.

Totals only, no $thisrow

The same answer can be written with both sides as totals. It is a group formula, with no Aggregate needed:

if(sumif(Entry_Type = "Consumption" and Inventory_Posting_Group in ("RM", "BYPROD", "SCRAP", "SCRAPS"), Quantity) > 0,
   sumif(Entry_Type = "Output" and Inventory_Posting_Group = "WIP" and Quantity > 0, Quantity / 1000),
   0)

It gives the same figures. Use whichever reads better to you: the $thisrow version is the Excel helper column; this one says “if the order consumed, its WIP output”.

In production: per order, month and year

This is the formula as it runs on a Business Central item ledger (Costing_Data), with one row per production order, month and year:

if(sumif(Document_No = $thisrow.Document_No and year = $thisrow.year and month = $thisrow.month and
         Entry_Type = 'Consumption' and Inventory_Posting_Group in ('RM', 'BYPROD', 'SCRAP', 'SCRAPS'), Quantity) < 0
   and Entry_Type = 'Output' and Inventory_Posting_Group = 'WIP' and Quantity > 0,
   Quantity / 1000, 0)

It differs from the walkthrough above in three ways:

The dataset

year and month in the formula are those two formula columns, named as on their cards (capitals don’t matter). You don’t repeat their formulas: Analytics puts each one’s own expression in, the same one it groups by.

What runs in the database

One plain GROUP BY on the order, month and year. No window, no subquery, one pass over the ledger. Readable form (the real statement also ignores capitals and treats blanks as 0):

SELECT
  CASE WHEN SUM(CASE WHEN "Entry_Type" = 'Consumption'
                      AND "Inventory_Posting_Group" IN ('RM', 'BYPROD', 'SCRAP', 'SCRAPS')
                     THEN "Quantity" END) < 0
       THEN SUM(CASE WHEN "Entry_Type" = 'Output'
                      AND "Inventory_Posting_Group" = 'WIP'
                      AND "Quantity" > 0
                     THEN "Quantity" / 1000 END)
       ELSE 0 END                               AS "custQty",
  TO_CHAR("Entry_Create_Date", 'FMMonth')       AS "month",
  EXTRACT(YEAR FROM "Entry_Create_Date")        AS "year",
  "Document_No"
FROM "Costing_Data"
GROUP BY TO_CHAR("Entry_Create_Date", 'FMMonth'),
         EXTRACT(YEAR FROM "Entry_Create_Date"),
         "Document_No"

The year = $thisrow.year and month = $thisrow.month part of the formula is the GROUP BY itself: each SUM adds up only its own order, month and year.

SELECT
  CASE WHEN (CASE WHEN COALESCE(SUM(CASE WHEN ((CASE WHEN LOWER(CAST(Costing_Data."Entry_Type" AS TEXT)) = LOWER('Consumption') THEN 1 ELSE 0 END = 1) AND (CASE WHEN LOWER(CAST(Costing_Data."Inventory_Posting_Group" AS TEXT)) IN (LOWER('RM'), LOWER('BYPROD'), LOWER('SCRAP'), LOWER('SCRAPS')) THEN 1 ELSE 0 END = 1)) THEN COALESCE(Costing_Data."Quantity", 0) END), 0) < 0 THEN 1 ELSE 0 END = 1) THEN COALESCE(SUM(CASE WHEN ((CASE WHEN LOWER(CAST(Costing_Data."Entry_Type" AS TEXT)) = LOWER('Output') THEN 1 ELSE 0 END = 1) AND (CASE WHEN LOWER(CAST(Costing_Data."Inventory_Posting_Group" AS TEXT)) = LOWER('WIP') THEN 1 ELSE 0 END = 1) AND (CASE WHEN Costing_Data."Quantity" > 0 THEN 1 ELSE 0 END = 1)) THEN COALESCE((CAST(COALESCE(Costing_Data."Quantity", 0) AS DOUBLE PRECISION) / NULLIF(1000, 0)), 0) END), 0) ELSE 0 END AS "custQty",
  TO_CHAR(Costing_Data."Entry_Create_Date", 'FMMonth') AS "month",
  EXTRACT(YEAR FROM Costing_Data."Entry_Create_Date") AS "year",
  Costing_Data."Document_No" AS "Document_No"
FROM "Costing_Data" Costing_Data
GROUP BY TO_CHAR(Costing_Data."Entry_Create_Date", 'FMMonth'), EXTRACT(YEAR FROM Costing_Data."Entry_Create_Date"), Costing_Data."Document_No"

Check your result

On this sample, consumption stored negative:

the statement above returns:

These figures were produced by running exactly this statement on Postgres.

If February’s output should count because the order consumed in January

Then the question is did the order consume at all? Take year = $thisrow.year and month = $thisrow.month out of the formula and keep the dataset as it is. You still get one row per order and month, but the consumption check covers the whole order, in any month or year. On the sample, two rows change: D1 February 0.8 (D1 consumed in January) and D6 January 2026 0.6 (D6 consumed in 2025). Every other row stays the same. Analytics adds each order’s monthly consumption across its months with SUM(…) OVER (PARTITION BY "Document_No"), over the grouped rows only, still one pass over the ledger.

The rules, in one place

1. A total (sum, sumif, countif) next to a row’s own value must say whose rows it adds up: Column = $thisrow.Column at the start of its condition.
2. With $thisrow, the formula is a row formula: one value per row.
3. To get one row per group, tick the grouping columns and set the formula’s Aggregate to SUM. Every $thisrow column must be ticked. You may tick more (Month, Item): the $thisrow total still covers what $thisrow says, e.g. the whole order.
4. Don’t tick columns the formula only reads, unless you want a row per value of them. And never fix an error by grouping: without $thisrow, grouping is what decides each total’s rows (see the fix that gives 0).
5. $thisrow only works as Column = $thisrow.Column joined with and inside a total. The column can be a formula column, by its name on the card; you don’t repeat its formula. Other uses aren’t available in SQL formulas; in a report, a UI formula can do more (04.12).
6. Text in straight quotes, "…" or '…', never curly ones. Capitals don’t matter. A blank counts as 0 in arithmetic and is never “above 0”.

When it says no

Every message names the column and the fix.

Where else this applies

The same pattern, count a row only when its group qualifies, comes up everywhere:

• Sales: an order line’s revenue, only for orders that were paid in full.
• Finance: a journal line’s amount, only for vouchers that balance to zero.
• Service: a ticket’s hours, only for tickets that were reopened at least once.
• Production: output only for orders that consumed, as here; or scrap only for orders above a planned quantity.

Each time: a sumif with Group = $thisrow.Group for the group’s condition, the row’s own check beside it, and Aggregate SUM by that same group.

Last updated 18 October 2026