Calculation code to build custom formulas

The following page describes the syntax you can apply when you want to build custom formulas in your report tables. You find the custom formula section here:

Custom formula related to segments

Custom formula related to the table

Multiline custom formula

If you want to create a multi line script, please use the return command in the start and ; in the end:

return (CellValue("Row1","Ginger_1_Average_f2bf-21db").Value 
    + CellValue("Row2","Ginger_1_Average_f2bf-21db").Value 
    + CellValue("Row3","Ginger_1_Average_f2bf-21db").Value 
    + CellValue("Row4","Ginger_1_Average_f2bf-21db").Value 
    + CellValue("Row5","Ginger_1_Average_f2bf-21db").Value) / 5;

Switching values / hardcoding values

Useful when needed to provide specific values into specific cells. Values can be either hardcoded or referenced from another cells.

switch(CurrentRowName)
{
  case "Row2": return 2314 * 12;
  case "Row3": return 2313 * 12;
  case "Row4": return 2294 * 12;
}
return 0;

The same syntax applies to columns. Use CurrentColumnName instead and prepare cases with the different row names.

If condition in custom formula

Nummeric comparision

The formula in the example below checks if the value in another column is below 0.2. If yes, it shows the value from that column. If not, it shows null (empty). This allows e.g. to anonymize individual results if count below X, or just in general transform results from one column to the other in a desired way. Returned values will also be used in charts series based on this column.

if ((double)CellValue(null,"Percent_6942-44f3").Value > 0.2)
{
  return CellValue(null,"Percent_6942-44f3").Value;
}
else
{
  return null;
}

The example below is taken from a table where negative differences should be exchanged with the value 0.

var result = CellValue("Row4",null).Value - CellValue("Row6",null).Value;
if (result < 0) {result=0;}
return result;

(double) prefix before CellValue() function needs to be used when cell value is compared to real value. It is not needed when CellValue() is compared to another CellValue() function or to integer number.

Combined if condition

The formula in the example below checks if an action needs to be taken based on two different inputs. This means the formula checks if the first condition is true, if not, it checks if the second condition is true.

var result = 0;
if (CellValue(null,"Column10").Value < 0)
{
    result = 1;
} 
else if (CellValue(null,"Column8").Value > 4.5m)
{
   result = 2;
} ;
return result;

String comparision

If you want to carry out string comparisons, you need to perform an explicit type conversion, as by default the return value of the formula is always a number

if (CellValue<string>(null,"[COLUMN-IDENTIFIER]").Value?.Equals("[STRING-VALUE]",  StringComparison.OrdinalIgnoreCase)==true) 
{
  return 1;
} 
else 
{
  return 2;
}

Note the explicit type conversion <string> after the term ‘CellValue’

If / Then in combination with CellTemplates

In the Context portal, columns are intended to display different actions. To achieve this, if-then conditions based on values in other columns are used in combination with CellTemplates.

If you use cell templates, the return value of 1 will return the first cell template and the return value of 2 will return the second cell template.

Round Values

In the following script the term is rounded to an number without decimal

Math.Round(((double)CellValue("Row2","Column1").Value/(double)CellValue("Row1","Column1").Value*100),0)

The full scope of the script editor is described in the dotnet language reference: