LISTSTDEV Function

• LISTSUM - Sum all values in the array. See LISTSUM Function.
• LISTMIN - Minimum value of all values in the array. See LISTMIN Function.
• LISTMAX - Maximum value of all values in the array. See LISTMAX Function.
• LISTAVERAGE - Average value of all values in the array. See LISTAVERAGE Function.
• LISTVAR - Variance of all values in the array. See LISTVAR Function.
• LISTSTDEV - Standard deviation of all values in the array. See LISTSTDEV Function.
• LISTMODE - Most common value of all values in the array. See LISTMODE Function.

Source:

For this example, you can generate some randomized data using the following steps. First, you need to seed an array with a range of values using the RANGE function:

derive type: single value: RANGE(5, 50, 5) as: 'myArray1'

Then, unpack this array, so you can add a random factor:

unnest col: myArray1 keys: '[0]', '[1]', '[2]', '[3]', '[4]', '[5]', '[6]', '[7]', '[8]', '[9]' pluck: true markLineage: true

Add the randomizing factor. Here, you are adding randomization around individual values:  x-1 < x < x+4.

set col: myArray1_0~myArray1_8 value: IF(RAND() > 0.5, $col + (5 * RAND()), $col - RAND())

To make the numbers easier to manipulate, you can round them to two decimal places:

set col: myArray1_0~myArray1_8 value: ROUND($col, 2)

Renest these columns into an array:

nest col: myArray1_0, myArray1_1, myArray1_2, myArray1_3, myArray1_4, myArray1_5, myArray1_6, myArray1_7, myArray1_8 into: array as: 'myArray2'

Delete the unused columns:

drop col: myArray1_0~myArray1_8,myArray1 action: Drop

Your data should look similar to the following:

Transform:

These steps demonstrate the individual math functions that you can apply to your list data without unnesting it:

Sum of all values in the array (list):

derive type: single value: NUMFORMAT(LISTSUM(myArray2), '#.##') as: 'arraySum'

Minimum of all values in the array (list):

derive type: single value: NUMFORMAT(LISTMIN(myArray2), '#.##') as: 'arrayMin' 

Maximum of all values in the array (list):

 derive type: single value: NUMFORMAT(LISTMAX(myArray2), '#.##') as: 'arrayMax'

Average of all values in the array (list):

 derive type: single value: NUMFORMAT(LISTAVERAGE(myArray2), '#.##') as: 'arrayAvg'

Variance of all values in the array (list):

 derive type: single value: NUMFORMAT(LISTVAR(myArray2), '#.##') as: 'arrayVar'

Standard deviation of all values in the array (list):

 derive type: single value: NUMFORMAT(LISTSTDEV(myArray2), '#.##') as: 'arrayStDv'

Mode (most common value) of all values in the array (list):

 derive type: single value: NUMFORMAT(LISTMODE(myArray2), '#.##') as: 'arrayMode'

Results:

Results for the first four math functions:

Results for the statistical functions:

Since all values are unique within an individual array, there is no most common value in any of them, which yields empty values for the arrayMode column.