Calculated Columns

With the Data Viewer App Part, you can add calculated columns to your Data Viewer views.

Microsoft® SharePoint® allows you to add a calculated column to a list or library. By creating a formula in these calculated columns you can include data from other columns and performs functions to calculate dates and times, to perform mathematical equations, or to manipulate text. In calculated columns, formulas are based on Microsoft® Excel® functions and syntax, but there are some limitations on the functions you can use and some subtleties in the syntax. You can find more information about the various kinds of formulas you can use in a SharePoint calculated columns in the Microsoft article Calculated Field Formulas.

Using the Data Viewer App Part, you add calculations similar to those you can add to a SharePoint calculated column plus you can add almost any calculation that you want. The advantage of using a Data Viewer calculated column is that the calculation is executed every time the page is displayed, whereas with a list / library calculated column, the formula is only executed when the list item or file is modified. Also, the Data Viewer calculated column, is not based on Microsoft Excel formulas, but on JavaScript based operators, calculations and functions.

As with other columns, with a Data Viewer calculated columns, you can apply conditional formatting on the calculated value, filter on the calculated column as well as sort or group by the result of the calculation. This page contains information on how to add a Data Viewer calculated column and information on formulas you can use. Other examples of calculated columns can be found on the Walkthroughs section.

Adding a Calculated Column

To add a Data Viewer calculated column, use the following steps:

1. Click the Add calculated column icon in the top right hand corner of the Columns tab.
   The Add calculated column dialog is displayed., which contains three sections:
   

• Column name: In the text box, type a name for the column.
• Column type: Select one of the following values: String, DateTime, Integer, Double or Boolean. You must select the data type that is to be calculated. This may not be the same value as the columns involved in the calculation.
• Expression: Use this section to create the formula for the calculated column. A formula can contain functions, column references, operators, and constants. To insert a column reference, select the column in the dropdown list and then click Insert field.

You must not type the column reference, you should always use the drop down list to insert references to columns. Examples of formulas can be found later on this page.

Once you have created finished configuring the formula, click Save.
The Add calculated column dialog box closes and the new calculated column is displayed at the top of the columns in the Columns Configuration section.

Select the check box to the left of the column if you wish to display the column in the Data Viewer App Part. You can then drag-and-drop the column so it appears in the required order in the App Part.


To edit the calculated column, click the Edit calculated column icon to the right of the column name.

To delete the calculated column, click the Remove calculated column icon.

← Go to top of section

Information on calculated column formulas

This section contains examples of formulas you can use in the Data Viewer calculated column. These formulas are based on the JavaScript language.

*JavaScript is a case-sensitive scripting language, which means is that the language considers capital letters as different from their lowercase counterparts. Keywords in JavaScript are all lowercase, for example, while, for, if and else. On the other hand, methods (properties) use lower CamelCase naming convention, that is, the first word is in lowercase and the first letter of each additional word is capitalized, for example, toLowerCase() and getFullYear().

Text formulas

You can use formulas to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, removing characters or spaces, and repeating characters.

1. To combine two or more columns, use the addition operator ( + ), for example to create a calculated column, fullname, which concatenates the columns, Firstname and Lastname, use the following formula:
      [Firstname]+" "+[Lastname]
   
   
2. Use String properties, for example, to find the length of the values stored in the Title column:
      [Title].length
   
   
3. Use String methods, for example,
   

• To convert a strings stored in the Title column to lower case,
     [Title].toLowerCase()
• To retrieve a substring for values stored in the Title column, starting from character position 5, for a length of 6 characters, where the first character of a string is position 0,
     [Title].substr(5,6)
• To remove all spaces for values stored in the Title column,
     [Title].replace(" ","")
  

Date and time formulas

JavaScript does not offer extensive date parsing or formatting. This section contains examples of manipulating out of the boxYou can use formulas to perform calculations that are based on dates and times, such as finding the day of the week, calculating the difference between two dates, and converting time to a decimal value.

1. Calculate the difference between two dates, use the subtraction operator ( – ) and then use Math object methods to present the difference as required, for example, when using a task list, create a calculated column using the following formula to display the number of days to complete a task.
      Math.floor(Math.abs([DueDate]-[StartDate])/(24*60*60*1000))
   
   
2. The date method, getMonth, returns a number that represents a month, for example, 0 is January, 1 is February. To return the name of the month use the following expression:
       ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'][([LastModifiedTime].getMonth())]
   
   
3. Many of the out-of-the-box managed properties that represent date values are provided as string in the format: YYYY-MM-DDTHH:MM:SSZ, such as, 2013-11-18T08:00:00Z.
   

• To display such managed properties without the characters, T and Z, use the expression,  [DueDateOWSDate].replace(/[TZ]/g,” “).
• To convert such values so you can subtract or compare values of a date/time use the formula, Date.parse([DueDateOWSDate])
  

Conditional Formulas

You can use the formulas to test the condition of a statement and return a value. Conditional expressions take the form of a ternary operator called the conditional expression operator, ?:, which use the following format:
  (condition)?(evaluate if condition was true):(evaluate if condition was false)

For example, if you want to create a calculated column that contains either Before or After, depending on the year the item / file was created, then the formula would look similar to:
   [DueDate]<[StartDate]?"Before":"After"

Mathematical Formula

You can use formulas to perform a variety of mathematical calculations, such as adding ( + ), subtracting ( – ), multiplying ( * ), dividing numbers ( / ), remainder after dividing a number by a second number ( % ) ; and using the Math object properties and methods, for example,

1. Round values in the NoTasks column to the nearest integral value:
      Math.round([NoTasks])
   
   
2. Calculate the area of a circle when the value of the radius is saved in the Radius column:
      Math.pow([Radius],2)*Math.PI
   

← Go to top of section

Copyright © 2024 Lightning Tools — Powered by