Running Total Statistical Functions

Running Total Statistical Functions calculate the standard deviation or variance using all values up to the current date for a fact or metric. Functions are available for standard deviation, variance, and versions of those functions for populations. See Standard Deviation Functions and Variance Functions.

Suppose you are tracking daily production for your enterprise throughout the month in a simple report tracking daily Units. You can use the RUNAVG and RUNSTDEV statistical functions to track the reliability of estimates as the month begins to close:

Date

Units

Running Avg Daily Units

Running StDev

06/02/2014

50

50.00

(blank value)

06/03/2014

60

55.00

7.07

06/04/2014

70

60.00

10.00

06/05/2014

50

57.50

9.57

06/06/2014

60

58.00

8.37

06/09/2014

80

61.67

11.69

06/10/2014

50

60.00

11.55

06/11/2014

70

61.25

11.26

06/12/2014

60

61.11

10.54

06/13/2014

80

63.00

11.60

Running total metrics can be broken down by two or more date attributes from the same date dimension. Non-date attributes are not supported.

Syntax

SELECT RUNSTDEV(...)
SELECT RUNSTDEVP(...)
SELECT RUNVAR(...)
SELECT RUNVARP(...)
SELECT RUNSTDEV(...) WITHIN (...)

The value for WITHIN must be a date attribute.

SELECT RUNSTDEV(...) WITHIN (ALL OTHER)

Examples

SELECT RUNVAR(Sales)
SELECT RUNSTDEVP(Leads) WHERE Year = THIS
SELECT RUNVARP(Returns) WHERE Amount > 500000
SELECT RUNSTDEV(Opportunities) WITHIN (Quarter/Year)