Calculated Fields¶

Calculated fields are used to convert raw values from existing data fields to calculated field values using Javascript functions, without modifying the existing data fields. Within each function, multiple basic data fields can be referenced.

Syntax¶

Each calculated field is a Javascript function.

Parameters¶

Name	Mandatory/Optional	Data Type	Description
rows	Mandatory	Object	Contains all data rows of a data field
row	Mandatory	Object	Reference of the current row
rowIndex	Mandatory	Number	The row number of the current row. The first row has a rowIndex of 0.
global	Mandatory	Object	Contains reference to all page parameters
locale	Optional	String	Contains multilingual configuration of content

Note

You don’t have to use all the parameters in your Javascript function.

Return Value¶

Value	Data Type	Description
value	Number/String (depends on configuration)	The calculated value

To use a parameter or any cell of each row, reference it like a Javascript object.

var cell_score = row['score'];
var global_param_time = global.time;

You may also use DTV built-in functions for your convenience.

DTVFunc.sum(fieldId): Returns the sum of all values of the data field
DTVFunc.average(fieldId): Returns the mean of all values of the data field

Examples¶

Converting Timestamp¶

You can use calculated fields to convert the raw Unix timestamp into a human-readable value. The function below converts the raw timestamp into the format yyyy/m/dd HH:MM:SS.

function(rows, row, rowIndex, global){
  var timestamp = row['timestamp'];
  var date = new Date(timestamp); // Converts the timestamp into a Date object
  var year = date.getFullYear();
  var month = date.getMonth() + 1;
  var day = date.getDate();
  var hour = date.getHours();
  var minute = date.getMinutes();
  var second = date.getSeconds();
  if (isNaN(year)) {
    return '';
  } else {
    return year + '/' + month + '/' + day + ' ' + hour + ':' + minute + ':' + second;
  }
}

Calculating Total¶

DTV only supports the aggregation of values column-wise. To aggregate values row-wise, you can create a calculated field. For example, assume there are 3 data fields, each providing the number of cars for a different car type, per year. You can use the function below to return the total number of cars in each year.

function(rows, row, rowIndex, global){
    return row['sedan'] + row['suv'] + row['other'];
}

Formatting Values¶

You can use calculated fields to format basic data fields. For example, values for electrical current can be positive or negative, depending on its direction. Most of the time, however, only the magnitude is required. The function below returns the magnitude of the electrical current.

function(rows, row, rowIndex, global){
    var newCurrent = row['current'];
    if (newCurrent < 0) { // If the value is negative, convert it to positive
    newCurrent *= -1;
    }
    return newCurrent;
}

Internationalization¶

You can write if logic scripts to determine the current language and support internationalized presentation of content. A sample is as follows:

function(rows, row, rowIndex, global){
    var statusMapping = {
      310101: {'zh-CN': '正常', 'en-US': 'Normal'},
      310104: {'zh-CN': '异常', 'en-US': 'Error'},
      310105: {'zh-CN': '未知', 'en-US': 'Unknow'}
    };

    var code = row["code"];

    var status = statusMapping[code];

    if (status) {
      return status[locale] || code;
    }

    return code;
}