Record Level Standard Scores

Version 2



    I would argue that standard scores, or z scores, are the canonical example of using an empty INCLUDE level of detail calculation to create record level compositions of aggregates and non-aggregates. Briefly, the standard score of n observations Xi subtracts the unbiased estimate of the mean from each observation, and then divides by the square root of the unbiased estimator of the variance.

    An empty INCLUDE level of detail ensures the value of an aggregate is made available to each individual dis-aggregated record, while also computing the aggregate of the dimensions included in the view. In the case of the standard score we wrap the average, AVG, and sample standard deviation, STDEV, in empty INCLUDE level of detail calculations.


    Example Calculation:


    // Wrapping the aggregates in an empty INCLUDE level of detail calculation ensures

    // the aggregates are computed over the dimensions selected on the worksheet.

    [Z] := ([X] - { INCLUDE : AVG([X]) }) / { INCLUDE : STDEV([X]) }




    Whenever I wrap an aggregate in an empty INCLUDE level of detail calculation I always cross validate in an appendix worksheet to ensure the aggregate is being calculated over the dimensions correctly.


    Related Functions:




    Further Reading/Examples:


    Shifting and re-scaling observations by aggregates calculated from the sample itself is a widely used method in signal processing, time series and longitudinal analysis, auto-regression, and stochastic processes. A small example will be attached shortly.