Creating measures with conditional expressions using the IF keyword

  • 1 October 2021
  • 0 replies
  • 93 views

Userlevel 1

With MAQL, you have the ability to create measures that use conditional expressions, allowing you to compute your more complex business requirements. One way to do so is to use the IF keyword. It looks like this:

SELECT IF condition THEN expression ELSE expression END

 

The IF keyword works just like it sounds: you will set a condition after “IF”, the value to use if that condition is met after “THEN”, and the value to use if the condition is not met after “ELSE”. Finally, you end the expression with “END”. The IF keyword can be used in both GoodData Platform and GoodData.CN. 

 

Here are a few examples of measures we can create in the metric editor: 

SELECT IF AVG(Duration) > 60 THEN 1 ELSE 0 END

SELECT IF Category = “Email” THEN SUM(Budget - Spend) ELSE SUM(Spend) END

SELECT SUM(SELECT IF Cost > 200 AND Supplier = “Acme Products” THEN Price * 2 ELSE Price END)

 

And those same examples as created in GoodData.CN using the API:

SELECT IF AVG({fact/call_center.duration}) > 60 THEN 1 ELSE 0 END

SELECT IF {label/campaign_channels.category} = “Email” THEN SUM({fact/campaigns.budget} - {fact/campaigns.spend} ELSE SUM({fact/campaigns.budget}) END

SELECT SUM(SELECT IF {fact/product_sourcing.cost} > 200 AND {label/product_sourcing.supplier} = “Acme Products” THEN {fact/product_info.price} * 2 ELSE {fact/product_info.price} END)

 

Defining your measure based on business logic

To help clarify, I’ll go over an example. Let’s say we have a fictional business and want to see how our revenue might be affected if we give a 20% discount on all products sold to customers in our “West” region. To make this assessment, we decide to simply look over our historical sales data, but reducing the revenue for “West” customers by 20%.

 

 We have the following LDM defined:

J9ZrMKvTKsA6ULoGqKbW0sZ7IbJRjK5X1swG-gYWZiA2EO2U5XE75I3a-OU2nSsKdmQ7BAOq7MnXngyvuq-x_6ChTTSmxJb4NDN11Xrft_0uTj16lP0QlGw8l3D5AOF1EQh8VvOI=s0

And we have a measure that computes our revenue, as follows:
SELECT SUM(Price * Quantity)

Or, when using the API to create the measure in GoodData.CN, this would like like:

SELECT SUM({fact/order_lines.price} * {fact/order_lines.quantity})

 

Our new measure with the discount, would look like this:

SELECT SUM(SELECT IF Region = "West" THEN Price * Quantity * 0.8 ELSE Price * Quantity END)

Or, when using the API to create the measure in GoodData.CN, this would like like:

SELECT SUM(SELECT IF {label/customers.region}=\”West\” THEN 0.8 * {fact/order_lines.region} * {fact/order_lines.price} ELSE {fact/order_lines.region} * {fact/order_lines.price} END)

 

We can create a line graph to compare the total revenue against the total revenue with the discount across time:

7TmXL_PSwSkRTVh5I9dANgQsFmK2L6AYQktRcLkIqA34c2lUnKtwJweZztww4ZQgXwQhVc_cotxKgVSoDdRkbo_iwd5LvIDcSbYFEUiigor7oAtVFQ-_KNobWdAiTC-uYWV610Oy=s0

 

Conclusion

The IF keyword can be useful in a lot of situations. However, some measures may require more than one conditional statement. For that, you can use the CASE keyword, which allows you to define multiple conditions. For more on that, check out our other article

 

For more on the IF keyword, read our documentation for GoodData Platform or the documentation for GoodData.CN

 

 

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


0 replies

Be the first to reply!

Reply