none
SP2013-Average of Total Time Difference (Calc Column) RRS feed

  • 질문

  • Hi:

    I have a listview with date and time fields, and a [Total Time] calculated field with a formula/formatting as follows:

    Columns: [Time In] and [Time Out]

    Formula:  =IF(OR(ISBLANK([Time Out]),ISBLANK([Time In])),"---",TEXT([Time Out]-[Time In],"h:mm:ss"))

    I need to display the Average of this calculated column in "h:mm:ss" format in ungrouped and possibly grouped views

    Is there a JQuery solution for this?

    Thanks

    Charlie E

    2018년 10월 16일 화요일 오후 12:27

답변

  • Hi Charlie,

    Add the following code into a script editor web part in list view page.

    <script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
    <script type="text/javascript">
    SP.SOD.executeFunc("clienttemplates.js", "SPClientTemplates", function() {
    	SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
    	  OnPostRender: function(ctx) {
    		var listId = _spPageContextInfo.pageListId.replace("{","").replace("}","");
    		var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists(guid'"+listId+"')/items?$select=Total_x0020_Time";
    		$.ajax({
    			url: url,
    			method: "GET",
    			headers: { "Accept": "application/json; odata=verbose" },
    			success: function (data) {				 
    				 var dataCount=0;
    				 var date = 0;
    				 $.each(data.d.results,function(i,item){
    					if(item.Total_x0020_Time!="---"){
    						dataCount++;						
    						var tarr = item.Total_x0020_Time.split(':');
    						date += new Date(0, 0, 0, tarr[0], tarr[1], tarr[2],0).getTime();
    					}
    				 });
    				 var avg = new Date(date/dataCount);
    				 var average=offsetify(avg.getHours()) + ':' + offsetify(avg.getMinutes()) + ':' + offsetify(avg.getSeconds());			
    				 var tbodyHtml='<tbody id="aggrWPQ1"><tr id="aggWPQ1">';
    				 $(".ms-listviewtable>thead>tr>th").each(function(){
    					if($(this).text().indexOf("Total Time")!=-1){
    						tbodyHtml+='<td class="ms-vb2"><nobr><b>Average=&nbsp;'+average+'</b></nobr></td>';
    					}else{
    						tbodyHtml+='<td></td>';
    					}
    				 });
    				 tbodyHtml+='</tr></tbody>';
    				 $("#aggrWPQ1").remove();
    				 $(".ms-listviewtable>tbody").eq(0).before(tbodyHtml);
    			},
    			error: function (error) {
    				console.log(JSON.stringify(error));
    			}
    		});
    	  }
    	});
    });
    function offsetify(t){
        return t < 10 ? '0' + t : t;
    }
    </script>

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    2018년 10월 17일 수요일 오전 7:35
    중재자

모든 응답

  • I need to display the Average of this calculated column in "h:mm:ss" format in ungrouped and possibly grouped views
    2018년 10월 16일 화요일 오후 6:53
  • Hi Charlie,

    Add the following code into a script editor web part in list view page.

    <script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
    <script type="text/javascript">
    SP.SOD.executeFunc("clienttemplates.js", "SPClientTemplates", function() {
    	SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
    	  OnPostRender: function(ctx) {
    		var listId = _spPageContextInfo.pageListId.replace("{","").replace("}","");
    		var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists(guid'"+listId+"')/items?$select=Total_x0020_Time";
    		$.ajax({
    			url: url,
    			method: "GET",
    			headers: { "Accept": "application/json; odata=verbose" },
    			success: function (data) {				 
    				 var dataCount=0;
    				 var date = 0;
    				 $.each(data.d.results,function(i,item){
    					if(item.Total_x0020_Time!="---"){
    						dataCount++;						
    						var tarr = item.Total_x0020_Time.split(':');
    						date += new Date(0, 0, 0, tarr[0], tarr[1], tarr[2],0).getTime();
    					}
    				 });
    				 var avg = new Date(date/dataCount);
    				 var average=offsetify(avg.getHours()) + ':' + offsetify(avg.getMinutes()) + ':' + offsetify(avg.getSeconds());			
    				 var tbodyHtml='<tbody id="aggrWPQ1"><tr id="aggWPQ1">';
    				 $(".ms-listviewtable>thead>tr>th").each(function(){
    					if($(this).text().indexOf("Total Time")!=-1){
    						tbodyHtml+='<td class="ms-vb2"><nobr><b>Average=&nbsp;'+average+'</b></nobr></td>';
    					}else{
    						tbodyHtml+='<td></td>';
    					}
    				 });
    				 tbodyHtml+='</tr></tbody>';
    				 $("#aggrWPQ1").remove();
    				 $(".ms-listviewtable>tbody").eq(0).before(tbodyHtml);
    			},
    			error: function (error) {
    				console.log(JSON.stringify(error));
    			}
    		});
    	  }
    	});
    });
    function offsetify(t){
        return t < 10 ? '0' + t : t;
    }
    </script>

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    2018년 10월 17일 수요일 오전 7:35
    중재자
  • Hi Charlie,

    Would you please provide us with an update on the status of your issue?

    Best Regards,
    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    2018년 10월 22일 월요일 오전 2:19
    중재자
  • Dennis!

    This is fantastic!  Thank you so much.

    Charlie


    Charlie E.

    2018년 10월 22일 월요일 오후 4:38