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 testcaption – 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)
|
