This documentation page assumes that you already have a SeekTable account. You can create free account by signing up.

Configure pivot table calculated field

It is possible to add formula-based dimension or measure in the cube configuration form ("Cube → Edit Configuration") with special "Expression" type. These expressions are calculated after data aggregation, and they could use another dimensions (or measures) as arguments. If you're looking for row-level calculations (before data aggregation) this is also possible, but these calculations are specific to the data source.

Calculated dimension

Lets assume that the cube has "Year" and "Quarter" dimensions, and we need a new dimension that combines year and quarter - something like "2018 Q1".

  1. Add new dimension with Type=Expression
  2. Set unique Name. For example, YearAndQuarter
  3. Set user friendly Label (optional). For example, Year + Quarter
  4. In Parameters add the following values:
    Value 1:
    Year + " Q" + Quarter (first value is an expression)
    Value 2:
    Year (argument, refers to an existing dimension name)
    Value 3:
    Quarter (argument, refers to an existing dimension name)

Add expression-based dimension

Calculated measure

Lets assume that the dataset has "Quantity" and "ItemPrice" columns, and cube has "SumOfQuantity" and "SumOfItemPrice" measures, and we need to calculate total price.

  1. Add new measure with Type=Expression
  2. Set unique Name. For example, TotalAmount
  3. Set user friendly Label (optional). For example, Total Amount
  4. In Parameters add the following values:
    Value 1:
    SumOfQuantity * SumOfItemPrice (first value is an expression)
    Value 2:
    SumOfQuantity (argument, refers to an existing measure name)
    Value 3:
    SumOfItemPrice (argument, refers to an existing measure name)

Add expression-based measure

Expression syntax

Expressions are evaluated by PivotData microservice; they could contain math operations (+, -, *, /), boolean operators (and, or, true/false constants), ternary conditional operator (<condition> ? <true_expression> : <false_expression>) context variables and special functions calls:

Function Description
IfNull(<value>, <value_if_null>) returns first argument if it is not null, otherwise second argument is returned
Format(<fmt_string>, new[] { <value1>, <value2> } ) formats a string with .NET String.Format. For example, fmt_string=${0:0.##} can be used to get output like "$5.27"
Type conversion functions
Convert.ToInt32(<some_value>) converts to integer data type
Convert.ToDecimal(<some_value>) converts to decimal data type (can contain fraction part)
Convert.ToDateTime(<some_value>) converts to DateTime data type
Convert.ToTimeSpan(<some_value>) converts to Time data type from strings like "hh:mm:ss"
Date functions
Date.Year(<date_value>) returns year value
Date.Quarter(<date_value>) returns quarter value: 1, 2, 3 or 4
Date.Month(<date_value>) returns month value
Date.Day(<date_value>) returns day value
Date.DayOfWeek(<date_value>) returns day-of-week value: Monday, Tuesday etc
Date.DayOfWeekShort(<date_value>) returns short day-of-week value: Mon, Tue etc