3

I wanted to created a calculated SharePoint column which gives me a sum of the monthly occurrence. For example:

enter image description here

During the month of July there were 7 occurrences so I wanted a calculated field to display occurrences for each month. How would I go about that?

2

You can accomplish this with a calculated field and a view.

Create a calculated field named Month and use the Date In or Date Out field (which ever you want to use to count occurrences). The calculation for this column will be =TEXT([DateField], "mm"). This will return the month as a numerical value.

Now that you have this, create a view or modify an existing and go to the Group By section. Do a group by on Month. On the section below that, Totals, select your Date In or Date Out field and select Count. Save the view.

You will now have your occurrences grouped by month with a count for total and for each month.

If you want to change the calculated field to read the month rather than a numerical value you can do that as well. Here's a post on that calculation get the month from a date column with the calculated column .

EDIT:

If you want to use a different column in the Totals section of the view you certainly can. The only issue with that is you can't use a calculated column this way. If you need to view a calculated column for this you could try using JSLink. Here's an article going over exactly that.

Another option if you don't want to use JSLink would be to instead of using a calculated column for your totals, is creating a workflow to run on a column and do the subtraction on your In and Out fields. Then you could use that field the same way as mentioned in the first part of the answer.

EDIT 2:

To tie the monthly occurrences to a particular vehicle, in the view you can add multiple group by's. You would want your first group by to be for the vehicle field followed by Month.

With everything expanded it should look something like this:

enter image description here

It looks messy due to everything being expanded and everything shown in the view, but functionally would look like this.

Edit 3:

Here are the only things that were changed from the default view to create what you see above.

enter image description here

enter image description here

  • thank you so much for this.... what would be the case if I want to Calculate the Monthly Total Miles? can I do it on the same view? I have a calculated column called "Total Miles" which is odIN-odOUT... – Raymound Aug 27 '15 at 13:57
  • Updated answer. – Jordan Aug 27 '15 at 14:21
  • thank you for that, I need to ask you about the first question I had about the Monthly Occurances... I have a dropdown field which contains "Vehicle Numbers" How would I make it so that the Total monthly occurance count shows for the Vehicle Number... for example... They can choice the Vehicle number first then display the total Count for a month they choice only for that Vehicle Number... – Raymound Aug 27 '15 at 14:36
  • Updated answer. – Jordan Aug 27 '15 at 14:48
  • that worked perfectly ... to the 2nd question I am thinking about just creating a new view. "Monthly Total Miles" would the calculate field for "Monthly Miles" be =TEXT([Total Miles],"mm")? FYI: Total Miles column is a calculated field I created =[Odometer In]-[Odometer Out] – Raymound Aug 27 '15 at 15:13
1

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back


Original answer:

In 2013 its best to apply CSR with a JSLink, but this method has some drawbacks like having to set the JSLink for every View, and not being able to (easily) have multiple Views on one page.

In SP2010 I used these steps to Sum a Calculated Column (which can not be done with a View Total)

It uses Javascript in a Calculated Column to do the math while the page is loading.

http://www.viewmaster365.com/#/Create/Sum shows how to built the Formula step by step. The complete Formula is:
(remember to set the datatype to Number)

="<div style=""color:"
 & IF(    [Rate]<[Buy]   ,"red","green")
 & """>"
 & DOLLAR(   [Qty]*[Rate]   )
 & "</div>"
 & "<div class=""vmSums"" style=""display:none;font-weight:bold;border-top:1px solid black;""></div>"
 & "<img src=""/_layouts/images/blank.gif"" onload=""{"
      &"var TBODY=this;while(TBODY.tagName!='TBODY'){TBODY=TBODY.parentNode}" 
     & ",dataSum='data-vmSum',dataCnt='data-vmCount',total=value="
 &    [Qty]*[Rate]   
     & ",count=0;"
     & "if(TBODY.getAttribute(dataSum)){"
         & "total=parseFloat(TBODY.getAttribute(dataSum))+value;"
         & "count=~~TBODY.getAttribute(dataCnt)+1}"
     & "TBODY.setAttribute(dataSum,Number(Math.round(total+'e2')+'e-2').toFixed(2));"
     & "TBODY.setAttribute(dataCnt,count);"
     & "window.clearTimeout(window.vmSumFunc);"
     & "window.vmSumFunc=window.setTimeout((function(){"
         & "var lastrow=~~TBODY.getAttribute(dataCnt),"
         & "el=document.getElementsByClassName('vmSums').item(lastrow);"
         & "el.textContent='$'+parseFloat(TBODY.getAttribute(dataSum)).toFixed(2);"
         & "el.style.display='block';"
     & "}), 100)"
 & "}"">"

Some things to note:

  • Calculated Columns set to datatype:Number can output HTML (since SP2010)
  • Using the SCRIPT tag was removed by Microsoft in summer 2013
  • but loading a blank IMG can still trigger Javascript in the onload function
  • This is all executed while the page is loading (so SP.js is not available and everything down the HTML page is not available yet)
  • Javascript Timeout (canceled by every next IMG onload) trick is used to execute the final Display of the Total once. Reference another DOM element with 'el' and you can display the total anywhere you want (note: this Javascript is loading Inline.. so your DOM element must exist)

CalcMaster Bookmarklet to edit Formulas

It is a PITA to debug Calculated Columns. Because you don't get feedback until you save a Formula and you end up having to click multiple times to get back to your Formula.

I have written a small 'CalcMaster' bookmarklet which hooks into the formula-editor and does a save of the Formula on every keypress; giving immediate feedback.
Recently published a first version on GitHub:

https://github.com/Danny-Engelman/CalcMaster

ICC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.