Hi everyone,
I have two columns. In the first column (Site) it either has the number 1 or 2 beside it. In the second column, there are some numerical values.
I have created a function which calculates rank and percentile. First, the function calculates in a new column the rank and percentile. Then the function selects all the rows that have the value 1 in site 1 and counts them. Then it selects and excludes all the rows with the value 2 in the site column and then calculates the rank and percentile in another column. All of this is working.
The problem comes in when I want to calculate the rank and percentile after selecting and excluding rows with the value 2 in the Site column. It doesn't return any errors, but there are no values in the column. I am assuming this has to do with the exclude() expression in my formula. If I remove the if statement from F3 so that it is exactly like F, I get the same values as the column beside it, but if I keep F3 the way it is, no values!
What am I missing? Sorry if this is very obvious...
rpFunction = Function( {row, test},
//row --> how many rows(devices) are in the data table?
//test --? which test are we calculating RP for?
Current Data Table( dtSummary );
col = Column( test );
newCol = "RP " || test;
F = Eval Expr( 100*(Col Rank( col )/Expr(row) ));
dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F ) ) );
col = Column(newCol);
col << delete formula;
dtSummary << Select Where(:Site==1);
row1 = nrows(dtSummary<<get selected rows());
dtSummary << Select Where(:Site==2);
row2 = nrows(dtSummary<<get selected rows());
dtSummary << Select Where(:Site == 2);
dtSummary << exclude;
newCol = "RP " || test || " Site 1";
F1 = Eval Expr( 100*(Col Rank( if(excluded(),.,col) )/Expr(row1) ));
dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F1 ) ) );
col = Column(newCol);
col << delete formula;
dtSummary << select where(:Site==2);
dtSummary << unexclude;
dtSummary << Select Where(:Site == 1);
dtSummary << exclude;
newCol = "RP " || test || " Site 2";
F2 = Eval Expr( 100*(Col Rank( if(excluded(),.,col) )/Expr(row2) ));
dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F2 ) ) );
col = Column(newCol);
col << delete formula;
dtSummary << select where(:Site==1);
dtSummary << unexclude;
);