The DataManipulation module is a powerful and flexible tool for calculating various mathematical, statistical, and financial functions. It offers over 30 built-in functions, as well as an easy mechanism for combining them into more complex formulas.

Calculating Functions

Functions are defined and calculated using the FunctionCalculator class. The first thing you must do when you need to use a function is to create an instance of this class:

Dim functionCalc As FunctionCalculator =  New FunctionCalculator()

FunctionCalculator functionCalc = new FunctionCalculator();

The second step is to define the data sources (arguments) for the function, which actually contain the input data for the function calculation. The arguments are standard DataSeries objects and most often these are the "Values" data series of some existing chart series (for example Line or Bar). To inform the function calculator that a DataSeries object will participate in the function calculation, you must add it to the Arguments collection. The following code demonstrates this, assuming that bar1 and bar2 are BarSeries objects, which are already added to the chart.

bar1.Values.Name = "Apples"
bar2.Values.Name = "Oranges"
functionCalc.Arguments.Add(bar1.Values)
functionCalc.Arguments.Add(bar2.Values)

bar1.Values.Name = "Apples";
bar2.Values.Name = "Oranges";
functionCalc.Arguments.Add(bar1.Values);
functionCalc.Arguments.Add(bar2.Values);

The third step is to define the function that will be calculated. This is done using the Expression property of the function calculator. It is a string that contains the formula of the function. For this example we will use the ADD function to add the values of the two bar series:

functionCalc.Expression = "ADD( Apples ; Oranges )"

functionCalc.Expression = "ADD( Apples ; Oranges )";

Now the function calculator is ready, and the only thing left to do is to call the Calculate method, which actually produces the result of the function. It returns a DataSeries object, which contains the result values.

Dim result As DataSeries =   functionCalc.Calculate()

DataSeries result = functionCalc.Calculate();

Now the "result" data series contains the values that are the result of the calculation. The Calculate method can be called multiple times with the same arguments and expression: this is useful when the values in the source data series are changed repeatedly, and you need to calculate a function with the new input values each time. 

If you have to change the arguments for the function, don't forget to call the FunctionCalculator.Arguments.Clear() method to remove the arguments for the previous calculations. 

When the calculation is done, the resulting data series can be used in many ways, but naturally the most common task is to present the result data in a chart.

Presenting the Function Data

The "result" data series can be presented in a chart easily, for example by assigning it to the "Values" data series of a line series (assuming that line1 is an existing LineSeries object):

line1.Values = result
chartControl1.Refresh()

line1.Values = result;
chartControl1.Refresh();

figure 1

Of course, any other charting type can be used to present the function data. The function "ADD" used in the example returns an array of values, where each value is equal to the sum of the corresponding values of the input arrays. Some functions do not return an array of values, but only a single value. For example, the expression "AVERAGE(Apples)" will calculate the average of all values in the "Apples" data series, and the result of this operation (the average value) will be the only item in the "result" data series. In such cases it is appropriate to use a constant line to present the value:

Dim cl As AxisConstLine =   chart.Axis(StandardAxis.PrimaryY).ConstLines.Add()
cl.Value = CType(result.GetValueForIndex(0), Double)
chartControl1.Refresh()

AxisConstLine cl = chart.Axis(StandardAxis.PrimaryY).ConstLines.Add();
cl.Value = (double)result.GetValueForIndex(0);
chartControl1.Refresh();

figure 2

Sometimes when the size of the resulting data series is smaller than the size of the input series (in general this happens with all function calculations that group the input data), a supplementary calculation of the X values of the resulting data series must be performed. This calculation is required in order to properly display the input and resulting data series in a single chart. For example, the "MAX(Apples; 2)" function calculates the maximum value of every two successive input values. This means that the size of the output array will be 2 times smaller than the size of the input array. If we don't provide some special x-coordinates for the output data, the chart will look like this:


figure 3 

As you can see, the logic of the chart is not presented properly. This situation can be avoided by using the CalculateXValues method of the FunctionCalculator class. The following code demonstrates how to use it:

line1.UseXValues = True
line1.XValues = functionCalc.CalculateXValues()
chartControl1.Refresh()

line1.UseXValues = true;
line1.XValues = functionCalc.CalculateXValues();
chartControl1.Refresh();

The chart produced is displayed in the figure below: 

figure 4

Expressions and Arguments

Understanding function expressions is crucial for using the data manipulation module because they define exactly what calculation is performed by the calculator object. The general structure of an expression is as follows: 

function_name( argument1; argument2; argument3 ... ) 

All the functions use a common syntax, although the type and the count of their arguments vary. The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by semicolons, and a closing parenthesis. In general an argument can be one of the following items:

• A data series name. When a data series name is encountered in the function formula, this means that the function will use the data series with that name as a data source.
• A constant. For example "ADD(Apples ; 3.52)" will add the constant 3.52 to each value in the "Apples" array.
• A function call. Functions can be used as arguments for other functions. When a function is used as an argument, or nested, the only requirement is that it return the same type of result that the outer level function needs. Various complex formulas can be composed with the help of the function nesting. For example, the formula of the Root Mean Square is as follows:

The corresponding expression for this function is: 

"POW( AVERAGE(POW(a;2)) ; 0.5 )" 

In this formula, 'a' is a data series argument, and 'POW' stands for the Power function, which first calculates the square of each element in 'a' and then the square root of the result of the AVERAGE function. In this case we have three levels of nesting.

Another example for function nesting is the financial function MACD (Moving Average Convergence Divergence). It is defined as the difference between a 26-day and 12-day exponential moving average. The expression for the MACD function is: 

"SUB(EMA(Arg;12); EMA(Arg;26))" 

As you may have noticed, in the previous examples we used function names like "ADD", "POW", "AVERAGE", "EMA", etc. These are some of the built-in functions in the data manipulation module. Here is a full list of the built-in functions. Each of them will be discussed individually in dedicated topics. 

Note that function expressions are case sensitive. If the name of an argument contains characters other than alphabetic characters, digits, and underscores, or starts with a digit, then it must be enclosed with curly braces {}. For example the name "Apple Sales" contains a space and cannot be used directly in an expression. 

MUL( Apple Sales ; 2 ) - syntax error
MUL( {Apple Sales} ; 2 ) - correct 

Here are some similar examples: 

ADD( values_123 ; -3.14 ) - correct
CUMSUM( 123values ) - syntax error (starts with a digit)
CUMSUM( {123values} ) - correct
SUM( data#1 ) - syntax error (contains a non-alpha character)
SUM( {data#1} ) - correct 

The data series that are added to the Arguments collection must have different names in order to be identified correctly. If you try to add a data series with a name that already exists in the Arguments collection, an exception will be thrown.