How to calculate a cumulative frequency using formula editor. please look at this example so that you have an idea what i mean
Cumulative Frequency, Percentiles and Quartiles | WyzAnt Resources
If you want a cumulative sum (with frequencies already computed like in the example), the following is the formula you get from Transform > Row > Cumulative Sum:
If( Row() == 1, 0, Lag() ) + If( Is Missing( :height ), 0, :height )
There is a more complicated version if you set BY variables though it only works if the table is already sorted by the BY variables.
Lag() with no arguments gives the previous value of the column being calculated.
xan
If you must sum within groups, and do not want to (or cannot) sort, you can still do it with a formula, but it is best entered in the script editor and pasted into the formula editor.
Cut/paste the formula below into the formula editor. Change the :X to whatever is being summed, and the :Group to whatever your group is.
If multiple grouping columns are needed, either concatenate them into a single column and use that as the grouping column, or add more statements with an & into the where clause.
Names Default To Here( 1 );
dt = Current Data Table();
Eval(
Eval Expr(
Sum(
:X[dt << getrowswhere(
:Group == Expr( :Group ) & Row() <= Expr( Row() )
)]
)
);
Cheers,
Brady
Thanks Xan and Brady,
if you need to reset the cumulative sum depending for example a "time" variable, how would you do it?
Valerie
For my formula, you can extend the row()==1 condition to reset the counter. For instance,
If( Row() == 1 | :time=0, 0, Lag() ) + If( Is Missing( :height ), 0, :height )
