In this tutorial, you will learn how to create and use filtering variables so you can quickly reuse them in different scenarios with data available in the GoodData Demo Workspace.
In Create and Save a Metric, you constructed the # of Canceled Orders metric defined as:
SELECT # of Orders WHERE Order Status = Canceled
You also have Revenue metric that excludes canceled and returned orders from being counted into your revenue:
SELECT Order Amount WHERE Order Status = Delivered.
What if you want to know how much more money you could have earned? To count your lost revenue, you could write a Lost Revenue metric defined as:
SELECT Order Amount WHERE Order Status IN
It will work but the logic that specifies which Order Statuses is included in your revenue metric is in two places now - in your Lost Revenue and in the # of Canceled Orders metrics.
MAQL, our Analytical Query Language, allows you to create reusable filters that we call variables.
Create Your First Filtering Variable
In the following exercise, you create a variable representing the “Lost Order” filtering rule.
- Click Manage and in the Data tab, click Variables.
- Click + Create Variable.
- Name the variable Lost Orders Filter.
- Change the variable type to Filtered Variable, then select Order Status as an underlying attribute.
In the Default Value section, click Edit.
Order Status options appear.
- Select the Order Status dropdown to is and tick off the Canceled and Returned options.
Note: Alternatively, you can choose isn’t and tick off Delivered option.
- Click Set.
- Click Save Changes.
The Lost Orders Filter variable now appears in your Variable list and you can use it to create expression in the Metric editor by selecting it from the Elements pane.
Create Metrics with a Variable
With your new Lost Orders Filter variable now available in your workspace, you can
- create new Lost Revenue metric
SELECT Order Amount WHERE Lost Orders Filter
- create new # Bogus Customers metric
SELECT COUNT (Customer ID) WHERE Lost Orders Filter
If, in the future, you include an Unpaid attribute in your data, you simply change the definition of the Lost Orders Filter variable and all your insights and metrics that use it will be automatically updated.