g_cumsum_clamp(G;S;O;X;F;C)

Returns the cumulative sum within a given group, "clamping" it so that it is never less than or greater than a given value.

Function type

Vector only

Syntax

g_cumsum_clamp(G;S;O;X;F;C)

Input

Argument Type Description
G any A space- or comma-separated list of column names

Rows are in the same group if their values for all of the columns listed in G are the same.

If G is omitted, all rows are considered to be in the same group.

If any of the columns listed in G contain N/A, the N/A value is considered a valid grouping value.
S integer The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation

If S is omitted, all rows will be considered by the function (subject to any prior row selections).

If any of the values in S are neither 1 nor 0, an error is returned.
O integer A space- or comma-separated list of column names that determine the row order within a particular group

If O is omitted, the order is the current display order of the table.

If any of the values in O are N/A, an error is returned.
X integer or decimal A column name

An N/A in X is treated like a 0.

F integer or decimal An optional value indicating the mimimum value (floor) at each new row.

If you use a column or expression, the floor can vary from row to row.

An N/A in F is treated like -infinity.
C integer or decimal An optional value indicating the maximum value (ceiling) at each new row.

If you use a column or expression, the ceiling can vary from row to row.

An N/A in C is treated like +infinity.

Return Value

For every row in each group defined by G and ordered by O (and for those rows where S=1, if specified), g_cumsum_clamp returns a decimal number corresponding to the sum of X for all rows up to and including that row, with a floor value of F and a ceiling value of C.

If no rows in a group have valid (non-N/A) values for X, the result for every row of the group is 0.

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_time_series_sample_usage"/>
<willbe name="g_cumsum_1" value="g_cumsum(state;include;order;value)"/>
<willbe name="g_cumsum_2" value="g_cumsum(state city;include;order;value)"/>
<willbe name="g_cumsum_clamp" value="g_cumsum_clamp(state;include;order;value;5;20)"/>

Example

<table cols="s,x,f,c">
1,10,0,30;1,-20,0,30;1,30,0,30;0,-40,0,30;1,50,0,30;
1,-60,0,;1,70,0,;1,-80,,80;1,90,,80;1,-100,-90,80;
1,110,,;1,-120,,;0,130,10,10;1,140,,;1,150,,;
</table>            

<!-- note 0 selection argument rows 4,13 --> 
<willbe name="r0" value="g_cumsum(;s;;x)"/>

<!-- no clamp, same as cumsum -->
<willbe name="r1" value="g_cumsum_clamp(;s;;x;;)"/>

<!-- constant floor only -->    
<willbe name="r2" value="g_cumsum_clamp(;s;;x;0;)"/> 

<!-- varying floor and ceiling -->  
<willbe name="r3" value="g_cumsum_clamp(;s;;x;f;c)"/>