Some functions

• PurgeChar
• WildMatch
• Dimensionality
• SecondaryDimensionality
• AGGR
• GetPossibleCount
• GetSelectedCount
• Concat
• IF
• $()
• TOTAL
• Alt

PurgeChar(s1, s2)
Returns the string s1 less all characters contained in string s2.
Example:
purgechar ( 'a1b2c3','123' ) returns 'abc'

WildMatch( str, expr1 [ , expr2,...exprN ] )
The wildmatch function performs a case insensitive comparison and permits the use of wildcard characters (* and ?) in the comparison strings.
Example:
wildmatch( M, 'ja*','fe?','mar')
returns 1 if M = January
returns 2 if M = fex

Dimensionality ( )
Returns the number of dimension columns that have non-aggregation content. i.e. do not contain partial sums or collapsed aggregates.
A typical use is in attribute expressions, when you want to apply different cell formatting depending on aggregation level of data.
This function is only available in charts.

SecondaryDimensionality ( )
Returns the number of dimension pivot table rows that have non-aggregation content. i.e. do not contain partial sums or collapsed aggregates. This function is the equivalent of the dimensionality() function for horizontal pivot table dimensions.
The secondarydimensionality() function always returns 0 when used outside of pivot tables.

AGGR ([ distinct | nodistinct ] [{set_expression}]expression {,dimension})
Returns a set of values of expression calculated over dimensions. The result can be compared to the expression column of a 'local chart', evaluated in the context where the aggr function resides. Each dimension must be a single field. It cannot be an expression (calculated dimension).
If the expression argument is preceded by the nodistinct qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure. If the expression argument is preceded by the distinct qualifier or if no qualifier is used at all, each combination of dimension values will generate only one return value.
By default, the aggregation function will aggregate over the set of possible records defined by the selection.
An alternative set of records can be defined by a Set Analysis expression.
By using this function in Add calculated dimension... it is possible to achieve nested chart aggregation in multiple levels. See also Nested Aggregations and Related Issues.
When used in chart expressions it is possible to achieve Sum of Rows in Pivot Tables.
Examples:
aggr( sum(Sales), Country )
aggr( nodistinct sum(Sales), Country )
aggr( sum(Sales), Country, Region )
count( aggr( sum(Sales), Country ))

GetPossibleCount (FieldName)
Returns the current number of possible values in a field.
Example
getpossiblecount ( Year )

GetSelectedCount (FieldName [, IncludeExcluded])
Returns the current number of selected values in a field.
If IncludeExcluded is true the count will include selected values, which are currently excluded by selections
in other fields. If false or omitted these values will not be included.
Examples
getselectedcount ( Year )
getselectedcount ( Year, true( ) )

Concat ([ distinct ] expression [, delimiter [, sort-weight]])
Returns the aggregated string concatenation of all values of expression iterated over a number of records as defined by a group by clause. Each value may be separated by the string found in delimiter. The order of concatenation may be determined by sort-weight. Sort-weight should return a numeric value where the lowest value will render the item to be sorted first. If the word distinct occurs before the expression, all duplicates will be disregarded.
Example:
Load Department, concat(Name,';') as NameList from abc.csv group by Department;

IF(condition , then , else)
The three parameters condition, then and else are all expressions. The first one, condition, is interpreted logically. The two other ones, then and else, can be of any type. They should preferably be of the same type. If condition is true, the function returns the value of the expression then. If condition is false, the function returns the value of the expression else.
Example:
if( Amount>= 0, 'OK', 'Alarm' )

TOTAL
If the word total occurs before the function arguments the calculation will be made over all possible values given the current selections but disregarding the chart dimension variables.
The total qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimension variables. In this case the calculation will be made disregarding all chart dimension variables except those listed, i.e. one value will be returned for each combination of field values in the listed dimension fields. Also fields which are not currently a dimension in a chart may be included in the list. This may be useful in the case of group dimensions, where the dimension fields are not fixed. Listing all of the variables in the group causes the function to work when the cycle or drill-down level changes.

Alt(case1[ , case2 , case3 , ...] , else)
The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.
Example:
alt( date#( dat , 'YYYY/MM/DD' ),
date#( dat , 'MM/DD/YYYY' ),
date#( dat , 'MM/DD/YY' ),
'No valid date' )
Will test if the field date contains a date according to any of the three specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text 'No valid date' will be returned (without any valid number representation).