Case Statement
If you want to calculate a KPI that handles multiple conditions, you may use the case statement when you need to compute a complex metric. In this article, we are going to discuss how to use the case statement in MAQL to define a metric in GoodData Platform and GoodData.CN.
The Case Statement in MAQL is different from its form in SQL, it performs on the aggregation dimension because it is designed to evaluate on multiple dimensions. But the syntax is similar, the MAQL syntax is:
| select <aggregation>(<case><when condition1 then outcome>, <when condition2 then outcome><else outcome><end>) |
Â
We recommend using the Case Statement inside the aggregation containers because this is the valid format in the Extensible Analytical Engine GoodData is using to ensure the KPI is calculated in the right dimensions. We can use the following Logical Data Model (LDM) for our example to discuss how to build KPI with the case statement:
Â
Diagram 1: LDM of Sales
| Type | ID | Table | Column |
| Fact | order_lines.quantity | Order lines | Quantity |
| Fact | order_lines.price | Order lines | Price |
| Date | date.<date granularity> | Date | Â |
Table 1: Selected LDM Metadata of Sales (Only needed for GoodData.CN)
Â
Let’s say we want to calculate the revenue while the price is 20% off on Wednesday, but the price is 20% more on Sunday, and the price stays the same on any other day in the week. The Syntax of this MAQL query would be:
| SELECT SUM(SELECT CASE WHEN date(day of week) = Wed Then 0.8*Price*Quantity, WHEN date(day of week) = Sun Then 1.2*Price*Quantity Else Price*Quantity End) |
Syntax 1A
Â
You may declare the KPI using Metric Editor like Digram 2 if you are using GoodData Platform:
Diagram 2: Declaring the KPI using Metric Editor on GoodData Platform
Â
If you are using GoodData.CN, you may declare the KPI via API with the following MAQL query:
| SELECT SUM(SELECT CASE WHEN {label/date.dayOfWeek}=\"3\" Then 0.8*{fact/order_lines.price}*{fact/order_lines.quantity}, WHEN {label/date.dayOfWeek}=\"0\" Then 1.2*{fact/order_lines.price}*{fact/order_lines.quantity} ELSE {fact/order_lines.price}*{fact/order_lines.quantity} end) |
Syntax 1B
Â
Note: In the default setting of the day of week, the numbers between 0-6 in string represent Sunday to Saturday.
Â
Case Statement would work if you declare the query without an aggregation container but we do not recommend doing so as the KPI may calculate in incorrect dimension and return an irrelevant result.
Â
The Case Statement provides the flexibility to calculate the KPIs in multiple conditions. If you would like to learn more about the details of the Case Statement, you may check out the documentation with the following links:
Â
GoodData Platform:
https://help.gooddata.com/doc/enterprise/en/dashboards-and-insights/maql-analytical-query-language/maql-expression-reference/logical-functions/case
Â
GoodData.CN:
https://www.gooddata.com/developers/cloud-native/doc/1.3/analytics/maql/conditionals/case/
Â
Â
If you are interested in GoodData.CN, please contact us.
Alternatively, test a trial version of GoodData Cloud:
Start Your GoodData Analytics Trial — Commitment-Free
Â