Skip to main content

Measure

A measure is an aggregation. It measures something from a sample (multiple records) and returns that measurement as a single value.

Example measure syntax
sum(dollars)           // total dollars
avg(price)             // average sales price
freq(segment==1)       // number of respondents belonging to segment 1
pct(aware_brand_17==1) // percent of respondents aware of brand 17
pct(q5 in (8,9,10))    // percent of respondents satisfied (top 3 box)

Definine a measure as an object​

A measure can be defined as an object. The only required property is syntax (which is also used as the id if id is not specified).

{ id: "m1", syntax: "sum(price)", label: "Optional measure label" }

Shorthand​

In many places, the shorthand for a measure can be used instead of an object.

"sum(price)" // just the syntax.

Extra payload​

Measures can contain arbitrary data. This extra information is ignored by calculation engine but could be used by data visualizations.

{ 
    id: "m1", 
    syntax: "sum(price)", 
    label: "Optional measure label",

    // extra payload:
    category: "Something",
    fillColor: "#3264c8",
    displayIndex: 9,
    pinned: true,
    extraInfo: "Something else",
    extraArr: [1, 2, 3]
}

Aggregation Functions​

A measure is defined by an Aggregation Function. A few examples follow below.

Calculating Descriptives​

Aggregation functions: mean, min, max, sum, validcount, more...

Examples of Descriptives
mean(q7)
min(price)
max(score)
sum(dollars)
validcount(q5b)

Calculating percentages​

Aggregation function: pct

Percentage Examples
// single value check
pct(aware_brand_01==1) // percent aware of brand

// multiple value check
pct(segment in (1,2)) // percent belonging to segment 1 or segment 2
pct(likely_to_recommend in (8,9,10)) // percent top box likely to recommend

// multiple field check
pct(purchased_product_01==1 or purchased_product_02==1) // percent purchased either product

// using and, or, parenthesis
pct(seg==1 and (q1_1==1 or q1_2==1))
tip

You must use a double equals "==" for equality checks, rather than a single "=". See Binary Operations.

Calculating Nets, Rollups​

A single response question rollup:​

pct(q10 in (1,2,3,5,7,10)) // percent who checked ANY of the specified options

A multi-response question rollup:​

pct(own_01==1 or own_02==1 or own_03==1) // percent who own ANY of the specified products

A post-aggregation net​

I want to calculate the average awareness for all brands. This takes two steps: Calculating the awareness for each brand, then averaging those awareness scores.

First calculate the awareness for each brand:

// measures axis
m_awareness: {
    pct_aware1: "pct(aware1==1)",
    pct_aware2: "pct(aware2==1)",
    pct_aware3: "pct(aware3==1)",
    pct_aware4: "pct(aware4==1)",
    pct_aware5: "pct(aware5==1)",
}

If you don't care about the individual scores and only want the net, you can just Aggregate again by adding another stage. If your measures are in the rows dimensions, just create an avg("m_awareness"). If your measures are columns, you'll need to add a field that equals "avg_fields(pct_aware1, pct_aware2, pct_aware3, pct_aware4, pct_aware5)". But the best thing is probably to AddWindowFields. Rob to improve this paragraph.

Null Handling​

This calculation excludes from the base all records where seg is null:

pct(seg!=1) // excludes missing values

This calculation includes all records in the base:

pct(isnull(seg,0)!=1) // includes all records by first converting missing values to zero

// or:
pct(seg ?? 0 != 1) // alternate syntax proposal

Re-basing Measures​

To reduce a base, use a conditional statement to nullify the expression for the exclusions:

// percent that like product only among those that own it
pct((own_product ? like_product : null)==1)

To increase a base, (e.g., among all respondents) see the Null Handling section above.

Multiple Aggregations in a Measure​

A measure cannot contain multiple aggregations. Consider the following scenarios and solutions.

Example: Net Promoter Score​

To compute a Net Promoter Score, we'll take '% Promoter' and subtract '% Detractor' using our Likely To Recommend field 'ltr'.

pct(ltr in (8,9,10)) - pct(ltr in (1,2,3)) // not allowed as a measure!!

The above syntax is not allowed because a Measure must be a single aggregation function.

Solution​

Calculate two measures in an Aggregate stage:

In measures section of Aggregate stage:
measures: {
    top_box_ltr: "pct(ltr in (8,9,10))", // top box likely to recommend
    bot_bot_ltr: "pct(ltr in (1,2,3))" // bottom box likely to recommend
}

Append an addFields stage to finish the calculation:

{
    $addFields: {
        nps: "(top_box_ltr - bot_box_ltr) * 100" // net promoter score
    }
}

Alternate Solution for NPS​

This workaround creates an on-the-fly recode of likely-to-recommend (ltr) and takes a mean of it:

In measures section of Aggregate stage:
measures: {
    nps: "mean(ltr in (1,2,3) ? -1 : ltr in (4,5,6,7) ? 0 : ltr in (8,9,10) ? 1 : null)"
}

Example: Average Sales Price​

I want to compute average sales price by dividing sum(dollars) by sum(units).

measures: {
    asp: "sum(dollars) / sum(units)" // not allowed as a measure!!
}

The above syntax is not allowed because a Measure must be a single aggregation function.

Solution​

Calculate two measures in an Aggregate stage:

measures: {
    total_dollars: "sum(dollars)",
    total_units: "sum(units)"
}

Append an addFields stage to finish the calculation:

{
    $addFields: {
        asp_bad: "total_dollars / total_units", // divide by zero vulnerability!
    }
}

This fixes the divide by zero vulnerability above, which could return undefined. Dividing by null is okay because it returns null. I might remove this paragraph because undefined is probably just returned as null.

{
    $addFields: {
        asp: "total_dollars / (total_units == 0 ? null : total_units)" 
    }
}