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.
Volumen, Promedio and Desviacion (decimal) to hold the results.// ============================================
// 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);
}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.if(m > 0) filter and use all twelve.(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.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 →