Using Javascript to Performa Calculation and display at the top of each column

[Iain Munro]

Afternoon

A few years ago, I received some code to perform some calculations and display the end result in the area where you add your Total Counts, Sums, Averages etc – You know, just above the Column heading in a SharePoint list – that was back in the nothingbutsharepoint days.

I have been looking throughout the web trying to come across this, but to no avail.

Does anything know the best way to achieve this ?

Iain

• This topic was modified 8 years, 1 month ago by Iain Munro.

[Alexander Bautz]

Did you receive this code from me?

This could be done by a script in a CEWP looking at all rows and updating the header based on the visible items in the list view. This method will only work for visible rows.

Could you provide some more details about what you want to achieve?

Alexander

[Iain Munro]

Hi Alexander

I cannot recall who I got the details from – it was back in 2008.

The example I was using at the time was calculating TRIR (safety stat) – we had our count of injuries and total hours, so in order to do the calculation. The calculation in the background using I think javascript or jquery would perform the calculation as follows – injuries x 200000 divided by total hours – it would then put above the column – TRIR – 2.4 as an example. It was live and would change as data was added.

So was just trying to see the best way to do this.

Iain

[Alexander Bautz]

If I understand correctly you need to perform this calculation on two values already summed up in the list header?

If you post a screenshot of your list view headers I’ll sketch up a solution for you.

Alexander

[Iain Munro]

Hi Alexander

Please see attached screenshot.

In the example I am working on, I have a number of packages which I can do a count on. I also created a day count between the date received and date uploaded – as that is a calculated field, I cannot add an Avg to it.

From there, what I want to be able to do is show the Average Days it takes to turn a package around.

If this is not clear, please let me know.

Appreciate the help

Iain

Attachments:

1. 

   2016-10-28_8-13-20.jpg
   

[Alexander Bautz]

Hi,
I though you would have both values in the list header, but with the “Days taken” not being summed up, I guess the best approach is to use a query that is not depending on the actual items in the view. Put this code in a Script editor web part in the page:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script type="text/javascript">
setTimeout(function(){
	(function(){
		jQuery.ajax({       
			"url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$filter=MOCNumber ne null",   
			"type": "GET",   
			"contentType": "application/json;odata=verbose",
			"headers":{ 
				"Accept": "application/json;odata=verbose",
				"X-RequestDigest" : jQuery("#__REQUESTDIGEST").val()
			},   
			"success": function(data){
				var totItems = data.d.results.length, totDays = 0, avgDays = 0;
				jQuery.each(data.d.results,function(i,item){
					totDays += Number(item.DaysTaken);
				});
				avgDays = totDays / totItems;
				jQuery("table.ms-listviewtable").before("<div style='color:green;font-size:22px;border:1px #cccccc solid;padding:3px;'>Average days: "+parseInt(avgDays,10)+"</div>")
			},
			"error":function(err){
				console.log(err);
			}
		});
	})();
},1000);
</script>

The filter on the query is set in the “url” parameter behind the “filter=”. Change this to match the filter you need to use.

You also need to ensure you have the correct FitldInternalName of your fields in the “MOCNumber” and “DaysTaken” columns.

This code will show a count as shown in the screenshot.

Let me know if you have any questions.

Please note that this code snippet if for SharePoint 2013 – if you need it for SP2010 I have to rewrite it slightly.

PS: None of the columns need to actually be in the view.

Hope you can use this approach.

Alexander

Attachments:

1. 

   CalcAvgDays.png
   

[Iain Munro]

Hi ALexander

This is exactly what I was looking for – having an issues getting it to work. I am getting an error.

Here is the code

<script src=”https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js”></script><script type=”text/javascript”>
setTimeout(function(){
(function(){
jQuery.ajax({
“url”: _spPageContextInfo.webServerRelativeUrl+”/_api/web/lists/GetById(‘”+_spPageContextInfo.pageListId.split(/{|}/).join(“”)+”‘)/items?$filter=MOC_x0020_Number”,
“type”: “GET”,
“contentType”: “application/json;odata=verbose”,
“headers”:{
“Accept”: “application/json;odata=verbose”,
“X-RequestDigest” : jQuery(“#__REQUESTDIGEST”).val()
},
“success”: function(data){
var totItems = data.d.results.length, totDays = 0, avgDays = 0;
jQuery.each(data.d.results,function(i,item){
totDays += Number(item.Days_x0020_Taken);
});
avgDays = totDays / totItems;
jQuery(“table.ms-listviewtable”).before(“<div style=’color:green;font-size:22px;border:1px #cccccc solid;padding:3px;’>Average days: “+parseInt(avgDays,10)+”</div>”)
},
“error”:function(err){
console.log(err);
}
});
})();
},1000);
</script>​<br/>​<br/>​<br/>

I get the Average Days above with NaN

Any ideas ?

Iain

[Alexander Bautz]

Hi,
The filter field are missing the operator and filter value.

Change:

?$filter=MOC_x0020_Number

to

?$filter=MOC_x0020_Number ne null

Alexander

[Iain Munro]

Many thanks Alexander.

I have added – now nothing shows up.

I tired in both a Script Editor and Content Web Editor.

Not sure why it is not showing up now – any ideas ?

Iain

[Alexander Bautz]

Are you using SharePoint 2013?

Press F12 to bring up the developer console, and click “Console”. Post any error messages you see there when you load the page.

Alexander

[Iain Munro]

Hi Alexander

Thanks for the reply.

Yes, I am using SharePoint 2013.

Found the error – it does not like the calculated column – see image.

Iain

Attachments:

1. 

   2016-11-02_8-50-09.jpg
   

[Alexander Bautz]

Hi,
I have changed the code to calculate the diff from the dates directly (no need for the calculated column). You must change the “DateReceived” and “DateShipped” to match your fields.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script type="text/javascript">
setTimeout(function(){
	(function(){
		jQuery.ajax({       
			"url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$top=5000",   
			"type": "GET",   
			"contentType": "application/json;odata=verbose",
			"headers":{ 
				"Accept": "application/json;odata=verbose",
				"X-RequestDigest" : jQuery("#__REQUESTDIGEST").val()
			},   
			"success": function(data){
				var from, to, totItems = 0, totDays = 0, avgDays = 0;
				jQuery.each(data.d.results,function(i,item){
					if(item.DateReceived !== null && item.DateShipped !== null){
						from = new Date(item.DateReceived);
						to = new Date(item.DateShipped);
						totDays += (to-from) / 86400000;
						totItems += 1;
					}
				});
				avgDays = totDays / totItems;
				jQuery("table.ms-listviewtable").before("<div style='color:green;font-size:22px;border:1px #cccccc solid;padding:3px;'>Average days: "+parseInt(avgDays,10)+"</div>");
			},
			"error":function(err){
				console.log(err);
			}
		});
	})();
},1000);
</script>

Alexander

[Iain Munro]

Hi Alexander

That was perfect – this worked just great.

Do you know if it is possible to have two or three measures like this side by side ?

Many thanks for turning around a working solution.

Speak to you soon.

Iain

[Alexander Bautz]

Yes, this is no problem – are all calculations from the same list?

If you can tell which calculations to do, I can show an example.

Alexander

[Iain Munro]

Thanks Alexander – I will think of something where I can have two or more that I need to show.

One last question – how can I make the Average Days number two decimal places ?

Iain

[Alexander Bautz]

Hi,
To have two decimals, just change this:

parseInt(avgDays,10)

to this:

avgDays.toFixed(2)

Alexander

[Iain Munro]

Hi Alexander

Just to keep it simple, what about having the Avg Days Late plus the total count, so I can get rid of the SharePoint default and keep everything on one line – see image

Iain

• This reply was modified 8 years ago by Iain Munro.

Attachments:

1. 

   2016-11-05_18-07-13.jpg
   

[Alexander Bautz]

Just change this line:

jQuery("table.ms-listviewtable").before("<div style='color:green;font-size:22px;border:1px #cccccc solid;padding:3px;'>Total number of items: "+totItems+" Average days late: "+parseInt(avgDays,10)+"</div>");

Alexander

[Iain Munro]

Hi Alexander

That is an awesome fix and good to use for all my views.

The issue with this is that it gives you the total items in the whole list, not by the sorted list I have in place – so I got 116 items, but there are only 335 in this current list as it is filtered to show those items without a certain date being completed – is there a way to do that ?

Iain

[Alexander Bautz]

Are you using a filter on the list view (set by editing the view) or a dynamic filter by using the filter on top of the list columns?

If you use a static filter it can be built into the REST query, but if you use dynamic filter it needs some more code to pull the active filter from the URL and use it in the query.

Alexander

[Iain Munro]

Hi Alexander

Thanks

I am just using the static filter built into the list view – field is not equal to blank etc.

Iain

[Alexander Bautz]

OK, please post the filter criteria here, but please provide the internal name of your fields.

Alexander

[Iain Munro]

Hi Alexander

Date Sent – Final
is equal to
<blank>

Field name is Date_x0020_Sent_x0020__x002d__x00

Iain

[Alexander Bautz]

I see, you should be able to change this line:

"url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$top=5000",   

like this:

"url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$filter=Date_x0020_Sent_x0020__x002d__x00 ne null&$top=5000",   

complete code:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script type="text/javascript">
setTimeout(function(){
	(function(){
		jQuery.ajax({       
			"url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$filter=Date_x0020_Sent_x0020__x002d__x00 ne null&$top=5000",   
			"type": "GET",   
			"contentType": "application/json;odata=verbose",
			"headers":{ 
				"Accept": "application/json;odata=verbose",
				"X-RequestDigest" : jQuery("#__REQUESTDIGEST").val()
			},   
			"success": function(data){
				var from, to, totItems = 0, totDays = 0, avgDays = 0;
				jQuery.each(data.d.results,function(i,item){
					if(item.DateReceived !== null && item.DateShipped !== null){
						from = new Date(item.DateReceived);
						to = new Date(item.DateShipped);
						totDays += (to-from) / 86400000;
						totItems += 1;
					}
				});
				avgDays = totDays / totItems;
				jQuery("table.ms-listviewtable").before("<div style='color:green;font-size:22px;border:1px #cccccc solid;padding:3px;'>Total number of items: "+totItems+" Average days late: "+parseInt(avgDays,10)+"</div>");
			},
			"error":function(err){
				console.log(err);
			}
		});
	})();
},1000);
</script>

Please note that you must change the field names in my code snippet to match yours.

Alexander

[Author]

Posts