# Thread: Displaying Trend in sparkline

1. Senior Member
Join Date
Mar 2009
Posts
205

## Displaying Trend in sparkline

Hello everyone,

I though I might share something I did, as I needed a way to display a trend line as a sparkline, like Excel does, and could not use MDX. Of course, I cannot display 2 curves in the same sparkline, but sometimes, the trend line is more useful than the data itself.

The BSH function I created accepts a CSV string containing the values for the sparkline, and returns a new set of values for the trend line. So you can use either or both.

BSH is really great !

I included a sample report.

NOTE: The existing commons-math library from apache included with pentaho is only at version 1.1. I needed 2.1 to get the newest multi-value linear regression functions.

I replaced the lib in PRD and BI-Server and I did ont see any problems so far. But no intensive testing.

To compute the new points, I use a level 4 polynomial function. It could be changed easily. It also could be adapted for a normal chart.

This is a sample report output:

The BSH function:

Code:
```import org.apache.commons.math.stat.regression.*;

String getTrend(String iData) {

OLSMultipleLinearRegression r = new OLSMultipleLinearRegression();
double[] y;
double[][] x;
double[] y2;
int i ;
int n ;
double[] params;
String[] dataArray = iData.split(",");

n = dataArray.length;
x =  new double[n][4];
y = new double[n];

// Initialize  X matrix and Y values
for (i =0;i<n;i++) {
x[i][0] =  i+1;
x[i][1] =  Math.pow(i+1, 2);
x[i][2] =  Math.pow(i+1, 3);
x[i][3] =  Math.pow(i+1, 4);

y[i] = Double.parseDouble(dataArray[i]);
}

// Calculate factors with regression (a)
r.newSampleData(y,x);
params = r.estimateRegressionParameters();

y2 = new double[n];
StringBuilder v = new StringBuilder();

//Recalculate new curve with level 4 polynomial function
// y = a4*x^4 + a3*x^3 + a2*x^2 + a1*x
for (i =0;i<n;i++) {
y2[i] =
(params[3]*Math.pow(i+1,4)) +
(params[2]*Math.pow(i+1,3)) +
(params[1]*Math.pow(i+1,2)) +
(params[0]*(i+1)) ;
v.append(Math.rint(y2[i]));
if (i < n-1) {
v.append(",");
}
}
return v.toString();
}

Object getValue() {
String data = dataRow.get("Value");
String trend = getTrend(data);
return trend;
}```
Last edited by haubuchon; 06-22-2010 at 09:30 PM.

2. Senior Member
Join Date
Mar 2009
Posts
205
After further testing, I saw what seems to be a conflict with mondrian, using commons-math library.

I'm not a java developer, si I'm asking to anyone out there if they know a way I could use in BSH (BeanShell) a different version of a jar than the one used in pentaho, (located in web-inf/lib) without causing a conflict with the existing jar ?

Thanks !

3. Senior Member
Join Date
Apr 2008
Posts
144
I would recommend posting the conflicting jar question in the mondrian forum with a link back to this thread. This should increase the probability of a response from someone with mondrian know-how.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•