Calculate Standard Deviation in Zoho Creator with Deluge

Calculate Standard Deviation in Zoho Creator

Averages hide the thing you usually want to know. Two products can sell the same annual volume while one ships steadily and the other spikes once a quarter — and only the second one wrecks your inventory planning. Standard deviation is what separates them, and Deluge has everything needed to calculate it.

The original produced a number, but not the right one. Four separate problems. N was wrong: the prose correctly said N is “how many months actually have sales in them”, but the code added the monthly sales values together and used that total as the sample size. The mean was truncated with .toLong() before being used in the variance calculation, so every deviation was measured from a rounded-off centre. The condition volume != null || volume > 0 used ||, which is true for any non-null value and so never actually screened anything. And the loop iterated a different table from the one N was derived from — squared deviations came from DATA_ENTRY rows while the divisor came from a record's monthly figures. The version below computes over one coherent set of values.

Before you start

  • A form with twelve monthly numeric fields, plus Volumen, Promedio and Desviacion (decimal) to hold the results.
  • Nothing else. This is pure Deluge with no external calls.

Deluge function

// ============================================
// SAMPLE STANDARD DEVIATION OVER MONTHLY VALUES
// ============================================

void Stats.calculateStdDev(int recordId)
{
    rec = DATA_ENTRY[ID == recordId];

    // --- 1. Gather the twelve months into one list ---
    months = List();
    months.add(ifnull(rec.Ene,0));
    months.add(ifnull(rec.Feb,0));
    months.add(ifnull(rec.Mar,0));
    months.add(ifnull(rec.Abr,0));
    months.add(ifnull(rec.May,0));
    months.add(ifnull(rec.Jun,0));
    months.add(ifnull(rec.Jul,0));
    months.add(ifnull(rec.Ago,0));
    months.add(ifnull(rec.Sept,0));
    months.add(ifnull(rec.Oct,0));
    months.add(ifnull(rec.Nov,0));
    months.add(ifnull(rec.Dic,0));

    // --- 2. Keep only months that actually traded ---
    values = List();
    total  = 0.0;
    for each m in months
    {
        if(m > 0)
        {
            values.add(m);
            total = total + m;
        }
    }

    // n is the COUNT of months with data. Not the sum of their values.
    n = values.size();

    if(n < 2)
    {
        info "Need at least two months of data for a sample standard deviation.";
        return;
    }

    // --- 3. Mean stays a decimal. Rounding here corrupts everything after. ---
    mean = total / n;

    // --- 4. Sum of squared deviations from the mean ---
    sumSquares = 0.0;
    for each v in values
    {
        diff = v - mean;
        sumSquares = sumSquares + (diff * diff);
    }

    // --- 5. Sample variance divides by n-1; population variance by n ---
    variance = sumSquares / (n - 1);
    stdDev   = variance.sqrt();

    rec.Volumen    = total;
    rec.Promedio   = mean.round(2);
    rec.Desviacion = stdDev.round(3);

    info "n=" + n + "  mean=" + mean.round(2) + "  sd=" + stdDev.round(3);
}

Notes

  • Sample or population? Dividing by n - 1 gives the sample standard deviation, which is right when your twelve months are a sample of ongoing trading. Divide by n for the population figure, when those twelve months are the entire universe you care about. Using n - 1 is the safer default and is what spreadsheet STDEV does.
  • Zero is not the same as missing. This treats a zero month as “no data” and excludes it, matching the original intent. If a genuine zero-sales month is meaningful in your data, drop the if(m > 0) filter and use all twelve.
  • Why the original wrote (0.1 - 0.1). That odd-looking expression was a way to force a decimal accumulator, because a plain 0 is an integer in Deluge and would make every subsequent addition integer arithmetic. Writing 0.0 does the same job and reads better, but the trick is worth recognising in older scripts.
  • Round at the end, never in the middle. Rounding the mean before squaring deviations is what broke the original. Carry full precision through the calculation and round only what you store.

This script is part of the free Creator Scripts Deluge Library.

All 39 Deluge scripts, the full Zoho Creator course, and every downloadable asset are now free. Get free access →

    • Related Articles

    • Complete Zoho Creator String Functions Guide

      Creator Scripts Zoho Trusted Partner in Digital Transformation Complete Zoho Creator String Functions Guide Essential String Functions 1. len() - String Length text_variable = "Hello World"; string_length = len(text_variable); // Returns: 11 2. ...
    • Work Around Deluge Iteration Limits in Zoho Creator

      Deluge caps how many records a single loop may touch. Hit that ceiling and your script stops part-way through, often without an error that makes the cause obvious. Two patterns get around it, and both work by making each individual loop smaller ...
    • Generate Random Numbers in Zoho Creator

      Deluge has no random(). That is a surprise the first time you need a reference code, a raffle winner or a sampling key. There are two honest ways around it, and which one you want depends on whether anybody would benefit from predicting the result. ...
    • Mass Update Records in a Zoho Creator Database

      Every price rises, every category gets renamed, every field eventually needs backfilling. The pattern is always the same: select the records you mean with a criteria set, loop them, write the change. The whole technique is two lines — what matters is ...
    • Integrating Zoho Creator with ChatGPT Using Deluge Script

      This script wires an OpenAI chat model directly into a Zoho Creator form. The user types a prompt into a text field, Deluge calls the OpenAI API with invokeurl, and the generated text lands in an output field on the same record — no middleware, no ...