Hi All, What would be the solution to execute a weighted average in MAQL ? does something like this would work ?

SELECT SUM(Numerator) / SUM(Denominator)

Where Numerator and Denominator are sub queries ?

Hi, you can use following MAQL if fact dataset contains facts value and weight.

select sum({fact/value} * {fact/weight}) / sum({fact/weight})

In case you need to calculate value as metric you may need to specify grain at which it is calculated. For example following MAQL

select sum(select {metric/value} * {metric/weight} by label/date.day) / sum(select {metric/weight} by label/date.day)

will calculate value and weight for each day as interim step and then the weighted average will be computed.

I created a query like this with the grain matching our data

SELECT (
  SUM(SELECT{fact/private_metric_mqlCount.value} * {fact/private_metric_mqlToSQL.value} by {label/Metric_Date.month}) /
  SUM (SELECT{fact/private_metric_mqlCount.value} by {label/Metric_Date.month})
)

You should not write

select fact/private_metric_mqlCount.value by {label/Metric_Date.month}

Fact table may contain multiple facts for each month. You need to specify aggregation how to reduce number of facts to single value per month. E.g.

select sum(fact/private_metric_mqlCount.value) by {label/Metric_Date.month}

or you can decompose it and make metric1:

select sum(fact/private_metric_mqlCount.value)

and reference it from the other metric

select {metric/metric1}  by {label/Metric_Date.month}

Our fact is connected to the date dimension in the data model. I used metrics instead facts to create the query as you mentioned in your second solution. I did not add the "by" in the query I am trying with the by right now. That said, My others dimensions are also not working. Each metric is linked to a set of dimensions, here a funnel and a channel

I used a date as example of attribute at which granularity the individual values for weigted average should be aggregated. If you need to calculate it from facts without aggregation then weight and fact need to be probably on the same grain=in the same row of the same dataset, otherwise the query engine may be not sure how to join the fact tables.

ok I understand

You can specify multiple dimensions in BY rule if you want to make finer breakdown of values and weight from which the weighted average is computed. E.g.

SELECT (
  SUM(SELECT{metric/mql_count_forecast} * {metric/mql_to_sql_forecast} by {label/Metric_Date.month},{label/funnel},{label/channel}) /
  SUM (SELECT {metric/mql_count_forecast} by {label/Metric_Date.month},{label/funnel},{label/channel})
)

The finer grain will make individual values smaller and thus also result of weighted average. Common dimensions can be used also for slicing of the result so you can calculate weighted average per funnel or per channel or per pair (funnel,channel).

Ok thanks a lot for this help. I have a value that is now displaying. What i can't understand is why I can't use the date as dimension filter while building a report with this new metric One last thing please, I would like to display a row, with each value aggregated by quarter. Once the weighted average metric is dropped into the list I do not have access to the date dimension anymore. All facts that are used in the sub metrics are linked to the date dataset.

I am not sure if there may be some limitation of grain you can use in filters if computation is done on month grain. But it seems you solved the problem with hidden filter so let’s hope it will be not issue. You are welcome.

I also removed the monthly grain since my data granularity is a month.