Calculate monthly occurrence

Question

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

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?

score 2 · Answer 1 · edited Apr 13 '17 at 12:41

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:

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.

edited Apr 13 '17 at 12:41

Community

1

answered Aug 27 '15 at 13:48

Jordan

2,814
8
27
46

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
Awesome. For that you would be able to create another view and do another group by on the months field. However you wouldn't be able to do the totals the same way as the count number (indicated in image above). You can see that for Vehicle 1 there was 100 miles in month 07 and 100 total miles in month 08. I wasn't able to display the totals like was done with the count because, with the views you cannot use the totals section on a calculated field. If you needed to do that you would have to look at using something like JSLink (as mentioned in first edit or in Danny's answer). – Jordan Aug 27 '15 at 15:35
how do I view the data information like you have in the above image... for vechicle two you are showing the total miles for both records 25 and 75... how do I view that information... if the user can see that they can do the calculation themselves... – Raymound Aug 27 '15 at 18:52
If you have your fields set up, and it sounds like you do (oIn, oOut, and Total Miles). All you would need to do is show them in the view. Go in and check the boxes next to the name of the field in the same place you are doing the group by and totaling. – Jordan Aug 27 '15 at 18:54
its adding like this http://i59.tinypic.com/1547cj9.jpg and this is the other image of my settings http://i57.tinypic.com/20p7br4.jpg – Raymound Aug 27 '15 at 19:03
Add another vehicle along with more than one month and the data should look more appropriate. – Jordan Aug 27 '15 at 19:06
ONCE I choose the group by setting in the edit view, the data disappears in the view... can you show how u have you view set up? – Raymound Aug 27 '15 at 19:41
Updated answer. – Jordan Aug 27 '15 at 19:48
I think its doing that because my month column is calculated "=TEXT([Date/Time In],"mm")", but isn't month suppose to be calculated? – Raymound Aug 27 '15 at 20:02
Month is calculated but the only reason is to parse out the In date. – Jordan Aug 28 '15 at 12:59

score 1 · Answer 2 · edited Jan 31 '19 at 07:49

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

Calculate monthly occurrence

2 Answers2

CalcMaster Bookmarklet to edit Formulas