QlikSense yield analysis: breakdown of the loss

Publications

QlikSense Yield Analysis: the Waste Nobody Is Watching

Hector Pincheira

Hector Pincheira

CIO | CTO
Digital Strategy & Technology Innovation

items

Thirty-eight, twenty-seven, twenty-one, fourteen. That is how yield loss is split in an average food plant: 38% process loss, 27% weight giveaway, 21% rework and reject, 14% recipe variance. Four causes with different owners, different costs and different fixes. In most systems, however, they show up added together in a single cell called «waste». A QlikSense yield analysis that fails to separate those four streams is no use for deciding anything; it is only good for confirming there is a problem, which is exactly what you already knew.

QlikSense yield analysis: process chain

And the problem is big. Many processing plants run with raw material yields between 50% and 70%, meaning that between a third and a half of the kilo that went in does not come out as sellable product. At the same time, raw material is close to 70% of a food producer's cost structure. Do the multiplication and you will understand why yield is the most expensive metric your plant still measures in a spreadsheet.

Yield is not one number, it is four

A pipe leaking at four different points is not fixed with a single patch. But the monthly production report, in the form most managers receive it, delivers exactly that: a single monthly percentage per plant. With that figure you can know you are doing badly. You cannot know where.

The minimum useful breakdown has four components:

  • Process loss: what is lost to drying, peeling, trimming, cutting. Technically unavoidable up to a point, and that point is the standard.
  • Weight giveaway: the pack that declares 500 grams and carries 512. Small per unit, brutal by volume.
  • Rework and reject: product that goes back or ends up as by-product. It costs twice, because it also consumes capacity.
  • Recipe variance: the operator who adjusts the mix and nobody updates the standard.

Each of those four is billed to a different role. Mixing them into a single indicator is the most efficient way to make sure nobody takes ownership.

QlikSense yield analysis: the model you have to load first

Before writing a single expression, the model has to be in shape. I have seen plenty of yield dashboards built on a model that does not distinguish between batches, and they end up averaging pears with apples. Literally, in an agribusiness.

One fact table per production order

The right grain is the production order, not the day or the shift. Each order has its consumed raw material, its declared finished product and its product family. Everything else aggregates up from there.

// --- Standard yield map by family and line ---
MapEstandar:
MAPPING LOAD
    Familia & '|' & Linea          AS %ClaveEstandar,
    RendimientoEstandar
FROM [lib://Datos/estandares_rendimiento.qvd] (qvd);
// --- Facts: one row per production order ---
Produccion:
LOAD
    OF,
    Fecha,
    Linea,
    Familia,
    Turno,
    KilosEntrada,
    KilosSalida,
    KilosReproceso,
    KilosRechazo,
    CostoKiloMP,
    // Actual yield of the order
    KilosSalida / KilosEntrada                      AS RendimientoReal,
    // Standard pulled from the map, with a fallback if missing
    ApplyMap('MapEstandar', Familia & '|' & Linea, 0.72)  AS RendimientoEstandar,
    // Kilos lost above what was expected
    KilosEntrada
      * ( ApplyMap('MapEstandar', Familia & '|' & Linea, 0.72)
          - (KilosSalida / KilosEntrada) )          AS KilosGap,
    // The same gap, valued
    KilosEntrada
      * ( ApplyMap('MapEstandar', Familia & '|' & Linea, 0.72)
          - (KilosSalida / KilosEntrada) )
      * CostoKiloMP                                 AS CostoGap
RESIDENT OF_Staging
WHERE KilosEntrada > 0;

Three decisions in that script deserve an explanation. The third parameter of ApplyMap() is the default value when the family-line combination does not exist in the standards master; without it, Qlik returns null and the gap evaporates silently, which is worse than an error. The WHERE KilosEntrada > 0 avoids the division by zero of cancelled orders. And the calculation of CostoGap is done in the script and not in the expression because the cost per kilo changes over time, so it must be frozen at the moment of the order.

The standard lives in a separate table

Never embed the standard in an expression. It is negotiated with operations, it changes by season and, if it sits in the code, every change needs a developer. A QVD table fed from a spreadsheet the plant manager maintains works better than anything elegant.

⚠️ Common error: loading the standard with a Join instead of ApplyMap(). The join duplicates rows when the master has validity dates, and suddenly your input kilos are multiplied by two without a single message warning you.

The expressions behind a QlikSense yield analysis

With the model in place, the rest is fairly short. And here comes the trap I have had to fix the most times.

Weighted yield, not an average of averages

// WRONG: averages 50 kg orders with 20,000 kg orders
Avg(RendimientoReal)
// RIGHT: yield weighted by volume
Sum(KilosSalida) / Sum(KilosEntrada)
// Previous period yield, for comparison
Sum({<Fecha = {">=$(=MonthStart(Max(Fecha),-1))<$(=MonthStart(Max(Fecha)))"}>} KilosSalida)
/
Sum({<Fecha = {">=$(=MonthStart(Max(Fecha),-1))<$(=MonthStart(Max(Fecha)))"}>} KilosEntrada)

The Avg() gives the same weight to a 50 kg sample order as to a full run. In plants with many small orders the difference between the two formulas exceeds two percentage points, and two points of yield in a food plant is a new car every year. The expansion $(=MonthStart(Max(Fecha),-1)) is evaluated only once before the set, so it is cheap even on tables of millions of rows.

Breaking down the gap against standard

// Cost of the gap, only where yield fell below standard
Sum( If(RendimientoReal < RendimientoEstandar, CostoGap, 0) )
// The 5 lines contributing most to the month's gap
=Aggr(
   If(
     Rank(
       Sum({<Fecha = {">=$(=MonthStart(Max(Fecha)))"}>}
           If(RendimientoReal < RendimientoEstandar, CostoGap, 0))
     ) <= 5,
     Linea
   ),
   Linea
)
// Percentage of orders that met the standard
Count( {<OF = {"=RendimientoReal >= RendimientoEstandar"}>} DISTINCT OF )
/
Count( DISTINCT OF )

The third expression uses an advanced search inside the set: {"=RendimientoReal >= RendimientoEstandar"} evaluates the condition for each value of OF and keeps the ones that satisfy it. It is faster than wrapping everything in an If(), because the filter happens at set level and not row by row. On a production order table spanning several years, the difference shows when you open the sheet.

What half a point is worth: QlikSense yield analysis in money

Here is the conversation the board actually cares about. Take a plant that processes 12,000 tonnes of raw material a year at a cost of 1,400 pesos per kilo. The annual raw material cost is 16,800 million. Half a point of yield equals 60 tonnes, that is 84 million pesos that stop going into the waste container.

That half point almost never comes from an investment in equipment. It comes from seeing the gap by line and shift at eight in the morning the next day, instead of seeing it aggregated on the 12th of the following month, when nobody remembers what happened with Tuesday's batch.

There is also a second-order effect that tends to be forgotten: when yield is measured by order and published by line, behaviour changes on its own. No incentive programme or internal campaign is needed; it is enough for the number to have a name attached and appear every day at the same hour. I have seen it more than once and it still surprises me.

Mistakes that ruin a QlikSense yield analysis

  1. Measuring yield without a standard. 68% means nothing on its own. It can be excellent for an in-shell walnut line and terrible for a raisin one. Without a standard per family, the dashboard generates arguments, not decisions.
  2. using Avg() on the percentage. We saw it above. It is the most common mistake and the most expensive, because it delivers a plausible and wrong number.
  3. Ignoring weight giveaway. 27% of the loss lives there and almost nobody measures it, because it requires matching declared weight against the packer's real weight. If you have connected scales, that match is money on the table.
  4. Loading only what is closed. If your script filters orders with a closed status, you lose precisely the ones that stayed open because of problems. The bias works against you: your apparent yield improves while the real one gets worse.

What I would do if I started tomorrow

I would start with a single line. The biggest one, the one that consumes the most raw material, and nothing else. A one-line dashboard that gets used every day is worth more than a fourteen-line one that gets opened at the monthly committee.

Then I would add shift as a dimension before anything else. In the plants where I have worked, the variation between shifts on the same line is usually bigger than the variation between different lines, and that finding alone points at training and supervision, not at equipment.

I would store the data in a daily QVD layer from day one, even if it looks like over-engineering at the start. When somebody asks eight months later how yield was running before the supplier change, you will want that history. If you left it in the transactional system, it is probably gone.

And I would agree the standard in writing, dated, signed by operations. This is not bureaucracy. It is the only way for next month's discussion to be about the cause of the gap and not about whether the standard was fair.

A warning from experience: the first version of the dashboard will show impossible yields, above 100% or below 30%. It is not an expression error, it is almost always the unit of measure. Kilos against boxes, kilos against units. That cost me two weeks of reloads the first time I built one of these.

One scale, two truths

In every plant two yield numbers coexist: the one the system reports and the one the shift supervisor knows. As long as those two numbers do not talk to each other, the dashboard is decoration. When they do talk, the plant starts recovering money that was already lost inside the budget.

The rest of this series, with the full model and the other industrial dashboards, is at hectorpincheira.com. Weigh the truck full, weigh the truck empty. The difference is everything you have.

Most Read

QlikSense Hierarchy: parent-child chart of accounts expanded by levels
QlikSense Hierarchy: the Chart of Accounts That Doesn't Add Up
QlikSense Sum TOTAL: share of each family over the total
QlikSense Sum TOTAL: the Percentage That Changes When You Filter
SAP Business One Inventory Revaluation - featured image of the tutorial
SAP Business One Inventory Revaluation: Fix Wrongly Loaded Costs
Empty chamber with wooden benches: the counterweight that required being present to be used
The Veto You Had to Say in Person: Your Decisions With No Counterweight
SAP Business One customizations - featured image of the pre-upgrade audit tutorial
SAP Business One Customizations: Pre-Upgrade Audit
SAP Business One Customer Concentration - featured image of the tutorial
SAP Business One Customer Concentration: Measure Your Risk with SQL

RELATED ARTICLES