US and Worldwide: +1 (866) 660-7555
Results 1 to 3 of 3

Thread: Displaying Trend in sparkline

  1. #1
    Join Date
    Mar 2009
    Posts
    201

    Default 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.

    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:

    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;
    }
    Attached Files Attached Files
    Last edited by haubuchon; 06-22-2010 at 09:30 PM.

  2. #2
    Join Date
    Mar 2009
    Posts
    201

    Default

    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. #3

    Default

    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
  •