Running Total Calculations

Comments

18 comments

  • Avatar
    Aviad Harell

    See the attached script to implement running sums by column in a pivot table.  Just make sure the fieldName variable in the script matches the name of the value you want to cumulate.

    Pivot.PNG

     

    widget.on('processresult', function(widget, queryResult) {
    	
    	//	USER SETTING
    	var fieldName = "Cumulated Quantity";
    
    	//	Figure out the cell index to use	
    	var panels = widget.metadata.panels;
    	var fields = $.grep(panels, function(w) { return w.name=="values"; })[0].items;		
    	var myField = $.grep(fields, function(w) { return w.jaql.title == fieldName; })[0];
    	var cellIndex = myField.field.index;
    
    	// Make sure the column index is found
    	if ($.isNumeric(cellIndex)) {
    
    		//	Get the rows for the table
    		var table = $(queryResult.result.table);
    		var rows = $('tbody',table).children();
    
    		//	Loop through each row and cumulate
    		$.each(rows, function(){
    			//	Get the cells for this row
    			var cells = $('td[fidx=' + cellIndex + ']',this);
    			//	Reset the running sum for this row
    			var runningSum = 0;
    			//	Loop through each cell
    			if (cells.length > 1) {
    				for (var i = 0; i<cells.length; i++) {
    					//	Get the value in this cell
    					var cellValue = $(cells[i]).text();
    					//	Make sure it's numeric
    					if ($.isNumeric(cellValue)) {
    						//	Cumulate the value
    						runningSum += parseFloat(cellValue);
    						//	Write the new value back to the cell
    						$(cells[i]).text(runningSum);
    					}
    				}
    			}
    		});	
    
    		//	Write back table to widget
    		queryResult.result.table = table[0].outerHTML;
    
    	}
    })



    PivotChart-CumulateColumns.js
    0
    Comment actions Permalink
  • Avatar
    Lian Yagoda

    See the attached script to create this running total for more than one series.

    var seriesNames = ["Austria", "Brazil","Finland","Germany"];
    
    widget.on('processresult', function(w,ev) {
    	
    	seriesNames.forEach(function(e, i, a){
    
    	var s = _.find(ev.result.series, function (ser) {return ser.name == e}).data;
    	
    	var ty = 0;
    	
    	for (var i = 0 ; i < s.length ; i++) {
    
    		ty = s[i].y += ty;
    	}
    });
    })
    



    for example:

    running_sum_line_chart.jpg

    0
    Comment actions Permalink
  • Avatar
    Shamir Colloff

    @Lian - this is super helpful, thank you!

     

    Is there any way to do this with an undefined list of series(es)?

    i.e., I am building a dashboard for multiple clients, and will use data security to segment the model between clients.

     

    The number of series(s) I want to plot depends on the number of projects each client has with us - it could be 1, or 100.

     

    Please advise, and thanks so much,

    Shamir

    0
    Comment actions Permalink
  • Avatar
    Lian Yagoda

    Hi Shamir,

    How would you like to determine which series to sum up and which ones don't, if not by their names? 

    0
    Comment actions Permalink
  • Avatar
    Shamir Colloff

    Hi Lian,

     

    I'd like a series for every "group by" value in my grouping column - I just want the measure over time to be a cumulative count (running total).  The challenge I have here is that it looks like I need to *define* those series' in advance ( the line that goes

    var seriesNames = ["Austria", "Brazil","Finland","Germany"];
    

     )

     

    and I don't know what the series names will always be.

     

    Sorry if that question doesn't make sense, let me know what you think?

     

    Thanks!

    Shamir

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    Hi Shamir,

    I believe then you should somehow, on EC level, prepare a table that will hold only one line with all names that currently exist for given dimension(I think this could be a little complicated; so in the end you'll get  ["Austria","Belgium"] as a single row value]). Then using JAQL within this script, maybe you could replace var values with a query that will retrieve that single line with all possible values and then use it as var. I guess that Lian could confirm whether this is possible at all :)

    Regards,

    Michał

    0
    Comment actions Permalink
  • Avatar
    Michal

    Hi Aviad,

    Great script for running sums by column in a pivot table!

    Can you please share the script for running sum by row in a pivot table?

    I tried to change your script by myself with no success...

    Thanks!

    0
    Comment actions Permalink
  • Avatar
    Tommy

    Hi Michal,

    I've attached a version of the script that will work with pivot rows.The implementation will be the same as the one that sums over columns




    pivot_running_sum.js
    results.JPG
    0
    Comment actions Permalink
  • Avatar
    Dean Krowitz

    Tommy thanks for that ... I was trying to use Lian's script with multiple series with no luck .. is there a way to apply this to ALL series by default rather than adding them manually to the script?

    0
    Comment actions Permalink
  • Avatar
    Robert

    Hi Tommy, I used your script but created my graph with the break coded into the values, not in the "BREAK BY" area.  I did this b/c I created a "Goal" value by increasing last year by a percentage (see attached jpg.  The graph work well except there is a straight line from the current month, February 2016, to the last X value, with the last value repeated at each X value.  Not sure if there is a fix for this.  Thanks!




    Trend.JPG
    0
    Comment actions Permalink
  • Avatar
    Walter

    This code seems to have worked for what I was trying to do: 

    ''''

    widget.on('processresult', function(w, ev) {
    var seriesNames = [];
    for(var i = 0; i < ev.result.series.length; i++)
    {
    seriesNames.push(ev.result.series[i].name);
    }

    seriesNames.forEach(function(e, i, a) {
    var s = _.find(ev.result.series, function(ser) {
    return ser.name == e
    }).data;

    var ty = 0;

    for (var i = 0; i < s.length; i++) {
    if (s[i].selected == false ) {
    ty = s[i].y += ty;
    }
    }
    });
    })

    ''''

    0
    Comment actions Permalink
  • Avatar
    Tommy

    Here's my script that works with multiple X-axis values, break bys, and multiple values. It will also work without those features and utilizes no hard coding, so it should be comprehensive enough to work in all scenarios for a line chart.

    Here's the code, which is also attached to the post. 

    widget.on('processresult', function(w, ev) {
    	for (i=0; i<ev.result.series.length; i++) {
    		var runSum=0;
    		for (j=0; j<ev.result.series[i].data.length; j++) {
    			//if selection Data is null or y is null, then we reached a break and reset the counter. Otherwise use the running Sum
    			if (ev.result.series[i].data[j] && ev.result.series[i].data[j].y) {
    				runSum+=ev.result.series[i].data[j].y;
    				ev.result.series[i].data[j].y=runSum;
    			}
    			else {
    				runSum=0;	
    			}
    		}
    	}
    } )

     

     results.JPG

     




    running_sum-line
    0
    Comment actions Permalink
  • Avatar
    Chris Morris

    Hi Tommy

    Thank you for the help, im trying to ajust your code to not ignore nulls at the moment do you have any advice

    Chris

    1
    Comment actions Permalink
  • Avatar
    Prasad

    Thanks tommy ur pivot_running_sum.js is very helpfull for me with 80/20 rule,

    if u can enplane code, it would be nice.

    0
    Comment actions Permalink
  • Avatar
    Nick Burleigh (Edited )

    Hi Tommy,

    Thank you for your code. I have found it very helpful so far. However, when filtering, if holes in the data emerge, runSum resets to 0 and the accumulation starts from scratch. For example, in the chart below I have values for various countries in southern Europe, but there is not a value for every year. Therefore, when the if-statement notices that there isn't a value, instead of carrying over the previous value, it resets to 0.

    So this is what is happening now:

    What would be logical is for the values for Spain (for example) to carry over into 2014-2017. Even though there's nothing being added to the values, the cumulative sum shouldn't just disappear.

    I've tried a couple of ways to accomplish this, but have had no luck so far. The first thing I tried was to get rid of the conditional statement altogether. I also tried simply adding 0 to runSum when the conditional returned false. These were moderately successful, but now the column segments in which no data is added are being grayed out:

    This is closer, but it still bothers me that they're grayed out.

    I'm wondering, do you know of any way to fix this? Any help or guidance would be very much appreciated.

    Thank you!

    Nick B.

    Edit: I just realized this is basically exactly what Chris Morris above was asking. I'll keep this here, as it seems like at least a few of us would appreciate some help with this.

    0
    Comment actions Permalink
  • Avatar
    Nick Burleigh

    Hi again,

    I just figured out the answer to my own question, so I thought I'd follow up. Here is the code I worked out, which ignores nulls but keeps the colors alive. Hope this helps!

    widget.on('processresult', function(w, ev) {
    for (i=0; i<ev.result.series.length; i++) {
    var runSum=0;
    for (j=0; j<ev.result.series[i].data.length; j++) {
    runSum+=ev.result.series[i].data[j].y;
    ev.result.series[i].data[j].y=runSum;
    ev.result.series[i].data[j].selected=false;
    }
    }
    });
    1
    Comment actions Permalink
  • Avatar
    Oxana Noa Umansky

    Thanks so much Nick!

    0
    Comment actions Permalink
  • Avatar
    Sanjoy Ghosh

    Hi,

    This script is not working when I upgraded Sisense to Version: 6.7.1.13022, can you please provide me the script that will work in Version: 6.7.1.13022

    Regards

    0
    Comment actions Permalink

Please sign in to leave a comment.