Syntax for dimension expressions

IMPORTANT
Read more about Data Workbench’s End-of-life announcement.

Dimension expressions are never used alone, but can be used anywhere a dimension is called for in a metric or filter expression.

  1. Underlined words should be entered in the expression text literally.
  2. The form {TEXT}? represents optional text.
  3. The form {TEXT}* represents text that may occur zero or more times.
  4. The form {A | B | C |...} represents text that consists of exactly one of the given options, such as A or B or C…
  5. The form [A,B) represents a range of numbers, from A up to but not including B.
Identifier

An identifier references a named dimension. For the rules governing legal identifiers, see Syntax for Identifiers.

Example: Sessions[ Session_Number = “1” ] is the number of Sessions that had a Session Number of “1.” Session Number is a named dimension referenced by identifier.

(Dimension)

The result of (Dimension) is the same as the result of Dimension. Parentheses specify the order of operations in an expression.

Example: Sessions[ (Page) = “/home” ] is the number of Sessions visiting the Page “/home”.

Dim by Level

Defines a dimension having the same elements as the dimension Dim, but relating to other dimensions through the dimension level.

Specifically, an element of the new dimension relates to the same elements of level as the same element of Dim, and relates to those elements of any other dimension that relate to any of those elements of level.

Example: Sessions[ (Page by Visitor)=”/home” ] is the number of Sessions of Visitors who visited the Page “/home”.

shift(Dim,Level,Group,N)

Defines a dimension having the same elements as the dimension Dim. The eth element of the dimension level relates to the same element of the new dimension as the element of Dim related to by the e+Nth element of Level, provided that the eth and e+Nth elements of level relate to the same element of the dimension group.

Example: Page_Views[ shift(Page, Page_View, Session, 1)=”/home” ] is the number of Page Views for which the next Page viewed in the same Session is “/home”.

next(Dim,Level,Group,N)
Similar to shift(Dim,Level,Group,N), except that if there are empty values in the dimension, they are skipped.
segment(Level {,String->Filter}*)

Defines a dimension that classifies elements of Level based on a list of filters. The elements of the new dimension are the strings given as arguments. Each element of Level relates to the 1st element of the segment dimension whose filter admits the element of Level. This is similar to the segment visualization.

Example: segment(Visitor, "One-Time Visitors" -> Visitor_Sessions = 1, "Very Loyal Visitors" -> Visitor_Sessions > 10, "Everyone Else" -> True) creates a dimension that classifies Visitors into three groups -- One-Time Visitors are those with only one Session, Very Loyal Visitors are those with more than ten Sessions, and all other Visitors have a value of "Everyone Else."

bucket(Level, Metric, Count, Format {, Start {, Size}? }?)

Defines a dimension whose elements are ranges of numbers (of fixed size, e.g, [0-9], [10-19],...). Elements of Level relate to the element of the bucket dim whose range contains the value of Metric for that element of level. Format is the printf format string used to format the elements of Metric.

Example: If Page_Duration_Minutes is a Page View-level dimension representing the number of minutes spent on each page, then bucket(Session, sum(Page_Duration_Minutes, Page_View), 100, "%0.0f minutes", 0, 5) is a Session-level dimension representing the number of minutes spent in each Session; its elements are 5 minute intervals {[0-5), [5-10),...,[495-500)}.

Start is the starting value of the first interval (default: 0) and Size is the size of the interval (default: 1).

prefix(Level {,ElementName->(Prefix{,Prefix}* )}* )

Defines a dimension whose elements are the given ElementName strings and are associated with the corresponding sets of Prefix strings. Elements of Level relate to the element of the prefix dim, which is associated with the longest prefix matched by the name of the given element of level. Prefixes ending with the special character '$' must be matched exactly.

For example, prefix(URI, "Products" -> ("/products/"), "Services" -> ("/services/", "/products/service/"), "Warranties" -> ("/products/warranty.html$", "/services/warranty.html$", "Everything Else" -> ("/"))) creates a dimension that classifies URIs into the four listed categories. The effect on various pages is as follows:

/products/warranty.html Goes into Warranty, since it matches the /products/warranty.html$ prefix exactly.

/products/cars/specialcar.html Goes into Products, since it matches the /products/ prefix and no longer prefix

/products/service/something.html Goes into Services, since it matches the /products/service/ prefix which is longer than the /products/ prefix.

/companyinfo/aboutus.html Goes into the "Everything Else" category, since the only prefix it matches is "/".

latency(Level, Clip, Dim, Filter, MaxBefore, MaxAfter, FormatString)
See Creating Latency Dimensions.
cartesian_product(Separator {,Dim}*)

Defines a dimension whose elements are all the combinations ("the cartesian product") of the elements of the dimensions given. The name of each element is made out of the concatenation of the corresponding elements in the input dimensions, separated by the given Separator string.

For example, if the dimension D1 has elements {"a", "b"} and the dimension D2 has the elements {"x", "y"}, then cartesian product("-", D1, D2) has the elements {"a-x", "a-y", "b-x", "b-y"}.

Note that internally, each of the input dimensions is treated as if the number of its elements is the next higher power of two. This results in the cartesian product having some dummy elements. When using the Data Workbench API, depending on the output format, these elements may be ommited, or they may be shown as "#nnn", where nnn is the ordinal of the element (and should be ignored by the client).

For example, in the example above, if D2 had the three elements {"x", "y", "z"}, it would be treated as if it had four elements, and the cartesian product would have the elements {"a-x", "a-y", "a-z", "#3", "b-x", "b-y", "b-z", "#7"}.

If no dimensions are given, the result is a dimension with one element, "#0", which is equivalent to the None dimension.

nearest_countable(Dim)
Refers to an already existing dimension: the nearest countable ancestor of Dim in the schema. For example, nearest countable(URI) is identical to Page_View.
normalized(Dim,Count)
Defines a normalized dimension from the denormal dimension Dim, with up to Count elements.
last_n(Dim, TimeMetric, FormatString, Count, Offset, TrimToData {, WeekStart}?)

Defines a dimension which has a subset of the elements of the dimension Dim, whose elements represent slices of time -- for example, days, weeks, or years.

The subset is a range around a specified time, the value of the constant metric TimeMetric, which is interpreted as a time value in seconds since midnight UTC of January 1, 1970. The range has Count elements, the last of which is Offset elements after the given Dim's element whose name is the result of formatting the value of the metric with the given FormatString string. FormatString uses the same % escapes as the standard C library function strftime.

If trimToData is true then any elements at the beginning of the resulting dimension, which would be before the beginning of Dim, are removed. When it is false, there will always be the exact number of elements specified by Count. Note that there may always be elements at the end of the resulting dimension that are not actually in Dim.

The optional WeekStart, if specified, must be one of { "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" }. It modifies TimeMetric by moving it backwards to the most recent occurence of that weekday.

Example: If Week has the elements { "10/03/10", "10/10/10", ..., "12/12/10" } and the built-in As Of metric has the value 1292348109 (representing a time in the middle of December 14th, 2010), then last n(Week, As_Of, "%m/%d/%y", 4, 0, false, "Sun") defines the dimension with elements { "12/12/10", "12/19/10", "12/23/10", "12/30/10" }.

Example 2: If the Week dimension only has elements {"12/19/10", "12/26/10", ..., "01/30/11"}, and the As Of metric is as above, then last n(Week, As_Of, "%m/%d/%y", 4, 0, true, "Sun") gives a dimension with elements {"12/19/10", "12/23/10", "12/30/10"}.

days_of_previous_months(Dim, TimeMetric, FormatString, nMonths, includeThisMonth, TrimToData)

Defines a dimension which has a subset of the elements of Dim, whose elements represent days. The subset is a range around a specified time, the value of the constant metric TimeMetric, which is interpreted as a time value in seconds since midnight UTC of January 1, 1970. The range will include the elements corresponding to each day in the nMonths months preceding the specified time. If includeThisMonth is true, the range also includes each day of the month that contains the specified time.

FormatString specifies the formatting of the elements of Dim, using "%" escapes as in the standard C library function strftime.

If trimToData is true then any elements at the beginning of the resulting dimension, which would be before the beginning of Dim, are removed. When it is false, there will always be the exact number of elements specified by Count. Note that there may always be elements at the end of the resulting dimension that are not actually in Dim.

Example: If Day has the elements { "01/01/10", "01/02/10", ..., "12/31/10" } and the built-in As Of metric has the value 1292348109 (representing a time in the middle of December 14th, 2010), then days of previous months(Day, As_Of, "%m/%d/%y", 2, false, false) will have elements { "10/01/10", "10/02/10", ..., "11/30/10" }.

days_of_current_month(Dim, TimeMetric, FormatString, allMonth, trimToData)
Similar to days of previous months, except the elements correspond only to days of the same month as the time specified by the TimeMetric. If allMonth is true, there will be an element for each day of the appropriate month; otherwise, only days from the first of the appropriate month through the day containing the specified time will be part of the dimension.
days_of_future_months(Dim, TimeMetric, FormatString, nMonths, includeThisMonth, TrimToData)
Similar to days of previous months, except that the elements correspond to the days of months after, rather than before, the month containing the time specified by the TimeMetric.
hours_of_day(Dim, Metric, TimeFormatString, nDaysForward, TrimData)

Defines a dimension which has a subset of the elements of Dim, whose elements represent hours. The subset is a range around a specified time, the value of the constant metric TimeMetric, which is interpreted as a time value in seconds since midnight UTC of January 1, 1970. The range includes the elements corresponding to each hour of the day nDaysForward after the day containing the time specified by the TimeMetric.

FormatString specifies the formatting of the elements of Dim, using "%" escapes as in the standard C library function strftime. The format string should always output a string representing midnight at the beginning of the day of the time passed in.

If trimToData is true then any elements at the beginning of the resulting dimension, which would be before the beginning of Dim, are removed. When it is false, there will always be the exact number of elements specified by Count. Note that there may always be elements at the end of the resulting dimension that are not actually in Dim.

Example: If Hour has the elements { "01/01/10 00:00", "01/01/10 01:00", ..., "12/31/10 23:00" }, and the built-in As Of metric has the value 1292348109 (representing a time in the middle of December 14th, 2010), then hours of day(Hour, As_Of, "%x 00:00", 0, false) has elements { "12/12/10 00:00", "12/12/10 01:00", ..., "12/12/10 23:00" }.

6586dc9d1b0434ed2e627930