Running Total Calculations
Introduction
Let's say you want to see the number of unit sold in a store list grow as you sell more units each day. Use a running total (also called a running count) to watch the number of units add up as you sell more units.
To calculate a running total, use the following procedure:
Steps
-
Create a column chart / line chart / area chart widget:
-
Go to “Edit Script:
-
Invert the following code to the widget script and click “Save”
widget.on('processresult', function(w,e) {
// running sum first series
var s = e.result.series[0].data;
var ty = 0;
for (var i = 0 ; i < s.length ; i++) {
ty = s[i].y += ty;
}
});
Modify the “series[x]” to refer the required series. In our case, it’s the 1st series (Quantity). Therefore, it’s set to “series[0]”
-
Refresh the widget and click “Apply”:
-
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.
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 -
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:
-
@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
-
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
-
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ł
-
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 -
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 -
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;
}
}
});
})''''
-
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; } } } } )
running_sum-line -
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.
-
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.
-
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;
}
}
});
Please sign in to leave a comment.
Comments
18 comments