Custom dimensions and measures

You can build custom dimensions and measures using Plywood. Add a Plywood-based dimension or measure to Pivot by entering it in the Formula field of a New dimension or New measure dialog box. See Creating dimensions and Creating measures for more information.

Prerequisites

Before you can build custom dimensions and measures, you must know the following:

• Dimension fields always start with a $ character in custom mode.
• Measures always start with a $main object in custom mode.
• Since measures are always aggregated values, the end result of a custom measure must be an aggregated value.
• Plywood functions are case-sensitive.
• Do not add a ; character to the end of your Plywood expression.
• You cannot build custom dimensions and measures based off other custom dimensions and measures.

Build nested or chained Plywood expressions

You can nest or chain Plywood expressions together. For example, if you are familiar with case, when, else logic from SQL or any programming language, you can create similar logic in Plywood using in, then, fallback expressions, like this: $cityName.in(['London','Aachen','Abbotsford']).then('Match Found').fallback('No Match'). fallback conditions can also contain other expressions, allowing you to create long fallback chains:

($imsi)
  .match('^20801151(6[3-9] ')
  .then('A')
  .fallback(
    ($imsi)
      .match('^20801151(7[0-2])')
      .then('B')
      .fallback(
        ($imsi)
          .match('^20801151(8)')
          .then('C')
          .fallback('D')
      )
  )

Common expressions for building custom dimensions

You must side-scroll to read the full contents of this table.

Here are some common expressions with which to build custom dimensions from the dimensions that already exist in your data cube:

Expression example	Applies to dimension type	Description
$dimension.length	String	Returns the character length for every row of the dimension.
$dimension.extract("([0-9]+)").fallback("missing")	String	This example extracts all numbers from every row of the dimension. If there are no numbers, it returns "missing". Use regex expressions to build extractions.
$dimension.transformCase('upperCase')	String	This example makes the entire string upper case. The function also accepts 'lowerCase'.
$dimension.concat('End of String')	String	This example adds 'End of String' to the end of the dimension's values.
$dimension.indexOf('World').fallback('Nothing matching')	String	Returns the index value for 'World' in the string for every row in the dimension. If 'World' is not found, it returns 'nothing matching'. If you don't specify a fallback, this function returns -1 when 'World' is not found.
$dimension.substr(x, y)	String	Returns x-y number of characters for every row in the dimension.
$cityName.in(['London','Aachen','Abbotsford'])	String, Numeric	Returns true if the value matches a value in the specified set; otherwise, returns false. You can specify the returned values like this: $cityName.in(['London','Aachen','Abbotsford']).then('Match Found').fallback('No Match')
$dimension.match('^Hel*')	String	Returns true or false based on whether the given regex expression is matched. You can specify the returned values like this: $dimension.match('^Hel*')then('Matched').fallback('Not matched')
$dimension.contains('hello')	String	Returns true or false based on whether the input string is matched. You can specify the returned values like this: $dimension.contains('hello').then('Hello Friend').fallback('Bye Friend')
$dimension.length()+2	Numeric	You can use math operations in custom expressions.
($dimension.length()-100).absolute()	Numeric	Returns an absolute value.
($dimension.length()).power(0.5)	Numeric	Raises the operand to the power that you specify.
$dimension.is('example')	String, Numeric, Boolean	Returns true if the value matches the input value and false if it does not. Only use single quotes with strings.
$dimension(value).and(2>1)	Boolean	Performs an AND operation between the value of the dimension and the second value that you input. Additional Boolean expressions include 'or', 'greaterOrEqual', 'greaterThan', 'lessThanOrEqual', 'lessThan', and 'not'.
$__time.timeBucket('PT5M')	Time	Creates a time bucket of the duration that you specify.
$__time.timeFloor('P1D','Asia/Shanghai')	Time	Floors the operand time to the nearest duration within the specified timezone.
$__time.timeShift('PT1M',-5)	Time	Shifts the operand time by the interval you specify within the given timezone. Do not use a + sign to shift time forward.
$__time.timePart('second_of_minute')	Time	Returns part of a timestamp. Additional Possibilities include 'minute_of_hour','minute_of_day','minute_of_week','minute_of_month', 'minute_of_year' 'hour_of_day','hour_of_week', 'hour_of_month', 'hour_of_year', 'day_of_week', 'day_of_month', 'day_of_year', 'week_of_month', 'week_of_year', and 'month_of_year'.
$dimension.cast('time')	Time, String, Number	Casts a number. Currently supported casts are: number to time (castType: 'time'), from number to string (castType: 'string'),from time to number (castType: 'number', and from string to number (castType: 'number').

Common expressions for building custom measures

You must side-scroll to read the full contents of this table.

Here are a few common Plywood expressions that you can use to build custom measures:

Custom Measure	Description
$main.count()	Counts all the rows in the data cube.
$main.countDistinct($dimension)	Counts the distinct values of the dimension.
$main.filter($dimension.is("true")).count()	Counts how many rows match a condition. This example counts how many rows in the dimension are true.
$main.sum($dimension())	Returns the sum of all values for the dimension.
$main.min($dimension())	Returns the smallest value of the dimension.
$main.max($dimension())	Returns the largest value of the dimension.
$main.average($dimension())	Returns the average value of the dimension.
$main.quantile($dimension(),0.x)	Returns the x quantile of the dimension. You cannot use a compound expression in this function.
$main.limit(x).count()	Limits the number of records to apply a given aggregate. This example returns x or fewer results.
$main.customAggregate('customAggregator')	Calls custom aggregate and returns the computed value.