measure
Definition
A measure is a computed numeric aggregation, which you would want to refer to in questions directly or in other measure definitions.
Usage
A measure is usually defined inside a top-level class (e.g. Order) and has the following properties:
kb {
class Order {
...
measure Revenue {
/** The name of the measure so you can refer to it in questions.
* If this measure is just an intermediate definition, which should not be used in questions, use display_name instead. */
name.en: "Revenue"
/** The return type of the measure. Currently, it can be either a number (or sometimes more specifically an integer). */
return: number
/** The definition of the measure. This is written in VQL (Veezoo Query Language), which is our optimized syntax to define measures. */
definition: """
sum(this.Order_Amount)
"""
}
}
}
Let's go over some examples to understand how to use measures in Veezoo.
Examples
Basic Measures
First of all, in Veezoo, unlike in other BI tools, you don't need to define a measure to be able to ask questions involving aggregations. For instance, you don't need to have an "average order amount" explicitly defined to be able to ask about it, since Veezoo knows what average means. This also applies to totals (e.g. total order amount), counts (e.g. number of users) and even percentages (e.g. percentage of First Class orders).
However, sometimes you will still want to define explicitly the measure. The main case we see in Veezoo is when the name of the measure doesn't capture completely the logic behind it. You may also want to add a description to it and display it more prominently in Autocomplete to guide users better.
Let's start with simple examples:
kb {
class Order {
...
/** A numeric value defined in the database. */
number Order_Amount {
...
}
measure Revenue {
name.en: "Revenue"
return: number
definition: """
sum(this.Order_Amount)
"""
}
measure Avg_Order_Value {
name.en: "Average Order Value"
return: number
definition: """
average(this.Order_Amount)
"""
}
measure Number_of_Orders {
name.en: "Number of Orders"
return: integer
definition: """
count(this)
"""
}
}
}
Measures are defined in VQL. A detailed explanation of this language can be found here.
The first thing you notice is the usage of the word this. It refers to the top-level class this measure is based on, in this case: kb.Order.
Measures with Filters
As mentioned before, a common case for measures is when there is a non-obvious logic to it, meaning it is not in the name of the measure itself. "Revenue in Germany" for instance does not need to be defined as a measure if Germany is just a country in the database. But maybe "Revenue" itself would require a flag 'is_cancelled' to be false or another filter to be set.
Boolean filters
kb {
class Order {
...
number Order_Amount { ... }
boolean is_Cancelled { ... }
measure Revenue {
name.en: "Revenue"
description.en: "Revenue only includes orders that were not cancelled"
return: number
definition: """
this.is_Cancelled = false
sum(this.Order_Amount)
"""
}
}
}
Alternatively, if the boolean filter needs to be true, we would have had this.is_Active = true.
Entity filters
What if we didn't have a boolean flag, but rather a specific entity (e.g. 'Active') that we needed to filter for?
kb {
class Order {
...
number Order_Amount { ... }
/** Possible Status: Cancelled, Active, On_Hold */
class Status { ... }
measure Revenue {
name.en: "Revenue"
description.en: "Revenue only includes orders that were active."
return: number
definition: """
this.Status = kb.Order.Status.Active # alternatively, you can also use a string directly, e.g. "Active"
sum(this.Order_Amount)
"""
}
}
}
What if you need to filter out an entity, e.g. not cancelled? Let's see just how the definition changes:
definition: """
this.Status != kb.Order.Status.Cancelled
sum(this.Order_Amount)
"""
Finally, maybe there are multiple statuses that you need to consider. For this case, we use a union:
definition: """
this.Status = union(kb.Order.Status.Active, kb.Order.Status.On_Hold)
sum(this.Order_Amount)
"""
Numeric filters
Let's say instead that to calculate Revenue, we should ignore all orders with negative Order_Amount.
definition: """
this.Order_Amount >= 0
sum(this.Order_Amount)
"""
For numeric filters, we support: <, <=, > and >=. We also support numeric operators like *, +, / and -.
Measures using other classes
What if for Revenue we actually need to filter out customers that are inactive (according to a separate class and table)?
This is how the measure definition would change:
kb {
class Order {
...
relationship from_Customer { ... }
measure Revenue {
name.en: "Revenue"
description.en: "Revenue only includes orders from active customers."
return: number
definition: """
var c: kb.Customer # think of this like the following SQL: FROM customers c
this.from_Customer = c
c.is_Active = true
sum(this.Order_Amount)
"""
}
}
}
This can be made shorter:
definition: """
this.from_Customer.is_Active = true
sum(this.Order_Amount)
"""
Now, let's go a bit wild. What if Revenue should be defined as total order amount only from customers that had at least 2 orders at any point in time.
definition: """
var c: kb.Customer
# c is this order's customer
this.from_Customer = c
var o: kb.Order # these are separate orders
o.from_Customer = c # from the same customer
# let's count these orders here for each customer
# the parenthesis are required
var number_of_orders = count(o) by (c)
# only those customers with at least 2 orders
number_of_orders >= 2
sum(this.Order_Amount)
"""
It is important to notice here that if you ask "Revenue this month", Veezoo will compute the revenue from orders this month with customers that at any point in time did at least 2 orders, not restricted to this month. This happens because we introduce a new order var o: kb.Order that does not have any of the filters from this.
Compositional Measures
Sometimes we will want to reuse existing measures in new definitions, because we depend on them or because we want to keep the code more readable.
Let's say we want to define gross profit margin in %. We will start by defining it in one measure.
kb {
class Order {
...
number Gross_Profit_Margin { ... }
number Sale_Price { ... }
measure Gross_Profit_Margin_Percentage {
name.en: "Gross Profit Margin (in %)"
return: number
# So we can see in %
unit: onto.Percentage
definition: """
var total_gpm = sum(this.Gross_Profit_Margin)
var total_sale = sum(this.Sale_Price)
(total_gpm / total_sale) * 100 # Multiply by 100 so we get a percentage
"""
}
}
}
Alternatively, you could also define it shorter as (sum(this.Gross_Profit_Margin) / sum(this.Sale_Price)) * 100.
Great. But this assumes that gross profit margin and sale price just require a sum. As we have seen before, these could be arbitrarily complex.
So let's say we have two measures Total_Gross_Profit_Margin and Total_Sale_Price. And now we will define it again using these measures.
kb {
class Order {
...
measure Total_Gross_Profit_Margin { ... }
measure Total_Sale_Price { ... }
measure Gross_Profit_Margin_Percentage {
name.en: "Gross Profit Margin (in %)"
return: number
# So we can see in %
unit: onto.Percentage
definition: """
var total_gpm = kb.Order.Total_Gross_Profit_Margin(this)
var total_sale = kb.Order.Total_Sale_Price(this)
(total_gpm / total_sale) * 100 # Multiply by 100 so we get a percentage
"""
}
}
}
The syntax to refer to a measure is similar to an aggregation function (e.g. sum, count), meaning it takes an argument. The argument is usually this, which is the top-level class with all the filters from the question.
Ratio Measures
Consider the following situation: You have a class Newsletter which has the Booleans opened and clicked. You now want to define the CTR (click through-rate), computed as the number of newsletters which are clicked, divided by the number of newsletters which are opened. With a simple measure one might (wrongly) try the following:
definition: """
var openedNewsletters = this
var clickedNewsletters = this
openedNewsletters.opened = true
clickedNewsletters.clicked = true
count(clickedNewsletters) / count(openedNewsletters)
"""
Unfortunately this doesn't work. The issue here is that this refers to one specific instance of the class Newsletter, meaning that openedNewsletter and clickedNewsletter refer to the same thing and so when the filters are applied, they are applied to both. This measure would always return 1.
The solution is to use define the ratio measure through other measures:
measure CTR {
name: "click through rate"
definition: """
kb.Newsletter.clickedLetters(this) / kb.Newsletter.openedLetters(this)
"""
return: number
}
measure openedLetters {
name: "opened Letters"
hidden: true
definition: """
this.opened = true
count(this)
"""
return: number
}
measure clickedLetters {
name: "clicked Letters"
hidden: true
definition: """
this.clicked = true
count(this)
"""
return: number
}
Veezoo makes sure in this case that the calculation of each measure does not affect the other.
Net Promoter Score (NPS)
A common example of a composite measure is the Net Promoter Score (NPS). The NPS is a market research metric based on a single survey question that asks respondents to rate the likelihood of recommending a company, product, or service to a friend or colleague. Respondents are asked to grade what is being surveyed on a scale from 1 to 10, indicating how much they like it. Users are then grouped into three categories: people answering 1–6 are considered detractors, 7–8 passives, and 9–10 promoters.
The NPS is then calculated as the percentage of promoters minus the number of detractors.
Here is how this would be implemented in Veezoo:
kb {
class Feedback {
name: "Feedback"
sql: "${Feedback.Id}"
number Value {
name: "Value"
sql: "${Feedback.Value}"
}
measure PercentageDetractors {
name: "Percentage of Detractors"
definition:
"""var detractors = proportion(this) {
this.Value <= 6
}
detractors"""
return: number
unit: onto.Percentage
}
measure PercentagePromoters {
name: "Percentage of Promoters"
definition:
"""var promoters = proportion(this) {
this.Value >= 9
}
promoters"""
return: number
unit: onto.Percentage
}
measure NPS {
name: "NPS"
definition: "kb.Feedback.PercentagePromoters(this) - kb.Feedback.PercentageDetractors(this)"
return: number
}
}
}
Measures with Multiple Arguments
As previously discussed, measures usually take as its argument the class that it is implemented in (which is then referred to as it this). Veezoo supports a more general case of measures as well.
By using the syntax arguments: [kb.Order, kb.Customer, ...], you can define any number of arguments for the measure. These arguments are then accessible via the keywords arg1, arg2, etc., in the order they appear in the array.
This approach is particularly useful for performing calculations involving multiple, unrelated classes. For example, consider calculating the Loss Ratio for an insurance company. The Loss Ratio could be defined as the total amount paid on Claims divided by the total premiums earned on Policies.
measure Loss_Ratio {
name.en: "Loss Ratio"
arguments: [ kb.Claim, kb.Policy ]
definition: """
sum(arg1.claimAmount) / sum(arg2.premiumAmount)
"""
return: number
}
In this scenario, it is necessary to use two arguments. If there were a relationship between Claim and Policy, one might be tempted to define a single-argument measure within kb.Claim as follows:
measure Loss_Ratio {
name.en: "Loss Ratio"
definition: """
var policy = this.Policy
sum(this.claimAmount) / sum(policy.premiumAmount)
"""
return: number
}
However, this approach would NOT work because it would filter out all Policies that do not have an associated Claim. By using multiple arguments, you ensure that all relevant data is included in the calculation.