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
$mainobject 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.
Common expressions for building custom dimensions
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
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. |