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)
"""
}
}
}
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.