Fields - Measure

Measure

Measure is used to perform aggregate functions in SQL (count, SUM, AVG, etc.) on multiple rows of a column.

Measures must reference dimensions.

fields:   #begin of fields section (inside View or Model)
- measure: measure_name
  hidden: false  
  label: 'field label in UI'
  description: 'description in UI'
  type: measure_type
  result: measure_result
  format_number: '$,.0f'
  currency_prefix: $
  currency_suffix: ''
  sql: BlockML reference of dimension (to be aggregated)
  #for measure of type '*_by_key': 
  sql_key: BlockML reference of dimension (to be used as distinct key) 
  #for measure of type 'percentile_by_key':
  percentile: 80

Measure Parameters

Name	Type	Default	Description
measure*	string	-	Field name
label	string	-	Override field name in UI
hidden	boolean	false	Hide field in UI
description	string	-	Field description in UI
type*	enum	-	Measure type: count_distinct - count unique values within a column sum - returns sum of values within a column sum_by_key - calculates proper sum for column values (even if joins leads to fanout) by pairing each value with distinct key average - returns average of values within a column average_by_key - calculates proper average for column values (even if joins leads to fanout) by pairing each value with distinct key median_by_key - calculates proper midpoint for column values (even if joins leads to fanout) by pairing each value with distinct key percentile_by_key - calculates proper value below which a given percentage of observations in a group of observations fall for column values (even if joins leads to fanout) by pairing each value with distinct key min - returns minimum value within a column max - returns maximum value within a column list - returns a list of unique values within a column custom - choose your own aggregation functions in sql parameter
result	enum	"number"	Resulting data type (after all SQL transformations, including aggregation function itself implicitly): string number - default for all measure types except "list" (string)
sql*	string	-	Way to calculate field through SQL expression (must be a BlockML reference of dimension)
sql_key**	string	-	(for measure of type sum_by_key, average_by_key, median_by_key, percentile_by_key) Must be a BlockML reference of dimension
percentile**	integer	-	(for measure of type percentile_by_key) Specify the fractional value (the Nth percentile) between 1 and 99
format_number	string	-	Format Number
currency_prefix	string	-	Format Number - Symbol
currency_suffix	string	-	Format Number - Symbol