Displaying Trend in sparkline

[haubuchon]

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.

You can download it here: http://commons.apache.org/math/

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:

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;
}

[haubuchon]

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 !

[Smoodo]

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.