The general pattern involves aggregating data by an attribute (such as month, quarter, category, or region) and then performing a secondary aggregation on the grouped results. In MAQL, this is done using the BY keyword, which changes the context of the metric and enforces dimensionality.

Next, we look at some examples of using BY.

Average Monthly Total Revenue

First, we calculate the Total Revenue for each month. Then we calculate the average.

• Total Monthly Revenue: SELECT SUM(Revenue) BY Month/Year
• Average Monthly Total Revenue: SELECT AVG(Total Monthly Revenue)

In MAQL syntax:

• SELECT SUM({fact/revenue}) BY {label/month}
• SELECT AVG({metric/total_monthly_revenue})

Average Quarterly Active Users

First, we calculate the Active Users for each quarter. Then we calculate the average.

• Active Users Quarterly: SELECT COUNT(User) BY Quarter/Year WHERE User Status = Active
• Average Quarterly Active Users: SELECT AVG(Active Users Quarterly)

In MAQL syntax:

• SELECT COUNT({attribute/user}) BY {label/quarter} WHERE {label/user_status} = "Active"
• SELECT AVG({metric/active_users_quarterly})

Average Category Amount

First, we calculate the Total Amount for each category. Then we calculate the average.

• Total Category Amount: SELECT SUM(Amount) BY Category
• Average Category Amount: SELECT AVG(Total Category Amount)

In MAQL syntax:

• SELECT SUM({fact/amount}) BY {label/category}
• SELECT AVG({metric/total_category_amount})

Average Customer Revenue

First, we calculate the Total Revenue for each customer. Then we calculate the average.

• Total Revenue per Customer: SELECT SUM(Revenue) BY Customer
• Average Customer Revenue: SELECT AVG(Total Revenue per Customer)

In MAQL syntax:

• SELECT SUM({fact/revenue}) BY {label/customer}
• SELECT AVG({total_revenue_per_customer})