Home arrow Reference arrow FilterByValue
FilterByValue
This function examines cell values in a data column relative to an aggregation of that column.  If it passes a test, then the requested action is taken on the rows.

Syntax:

FilterByValue, filterAction, criteria, axis, index, caption, boolThresholdAsPercent, threshold, value , boolUseValue, aggregation

Parameters:

filterAction – action to take on items that meet criteria { KeepByVal, RemoveByVal, JustSelect, Flag} see comment below

criteria – the criteria to be used when filtering { LessThan, LessThanOrEqualTo, NotEqual, Equal, GreaterThanOrEqualTo, GreaterThan, OutsideOfRange, WithinRange }

axis – which axis to filter  { Row, Column }

index - a zero-based row or column index to use for the filter test

caption – row or column caption to use instead of index above (set index to -1)

boolThresholdAsPercent – whether to treat the threshold as a percentage value (true) or not (false)

threshold –value of the threshold for the test relative to the aggregation

value – for Actual tests, the numeric value to be used  (set boolUseValue to true, aggregation to Actual)

boolUseValue - flag to instruct to use the value ( true ) or not ( false ) 

aggregation – aggregation of the data axis to be used for the test  (Actual, Average, Sum, Maximum, Minimum, First, Last, Median)

Examples:

FilterByValue,KeepByVal,LessThan,Column,-1,Data_One,false,0,100,true,Actual

Keep a row if the actual value in the column identified as “Data_One” is Less Than 100.

FilterByValue,KeepByVal,LessThan,Column,-1,Data_One,true,10,-1,false,Average

Keep a row if the value in the column identified as “Data_One” is 10% less than the column average..

FilterByValue,RemoveByVal,LessThan,Row,-1,Food-Attributes Temperature,False,0,11,True,Actual

Remove rows which have less that 11 in the column labelled ‘Food-Attributes Temperature’.

FilterByValue,JustSelect,WithinRange,Row,-1,Food-Attributes Main Course,False,1,0,True,Actual

Select rows whose value is within the range of 0 plus or minus 1.

FilterByValue,JustSelect,LessThanOrEqualTo,Row,-1,Food-Course Soup,True,0.2,0,False,Average

Select the rows where the column labelled ‘Food-Course Soup’ has a value less than or equal to 20% above average.

Comments:

JustSelect and Flag criteria work for Row filtering only. Flag inserts a Flag column to the left of the column being tested.

To clarify the values used, if an equality test, test against the value of:

value (boolUseValue=true) + threshold (boolThresholdAsPercent=false)

value (boolUseValue=true) + value*threshold% (boolThresholdAsPercent=true)

zero (boolUseValue=false, aggregation=Actual) + threshold (boolThresholdAsPercent=false)

zero (boolUseValue=false, aggregation=Actual) + zero*threshold% (boolThresholdAsPercent=true) [zero]

axis aggregation (boolUseValue=false, aggregation!=Actual) + threshold (boolThresholdAsPercent=false)

axis aggregation (boolUseValue=false, aggregation!=Actual) + axis aggregation*threshold% (boolThresholdAsPercent=true)

If a range test, use the range of:

value (boolUseValue=true) +/- threshold (boolThresholdAsPercent=false)

value (boolUseValue=true) +/- value*threshold% (boolThresholdAsPercent=true)

zero (boolUseValue=false, aggregation=Actual) +/- threshold (boolThresholdAsPercent=false)

zero (boolUseValue=false, aggregation=Actual) +/- zero*threshold% (boolThresholdAsPercent=true)

[zero]axis aggregation (boolUseValue=false, aggregation!=Actual) +/- threshold (boolThresholdAsPercent=false)

axis aggregation (boolUseValue=false, aggregation!=Actual) +/-

axis aggregation*threshold% (boolThresholdAsPercent=true)

 

Reference

Installation - Java