Hi. Is there any way to do a window function in MAQL? I'm trying to do a

sum over(partition by id) / count(distinct id)

sorta thing. I have a table with id and parent_id. I have another table with metric/fact that I have to AVG on parent_id (it contans only parent_id and matric). In GD LDM I have the table with id and parent id joined with a m-n to the table with parent_id and metric/fact. Whatever I try with BY, BY ALL etc doesn't actually calculate the AVG I want (by parent_id), it always calculates by the id. In the chart I use filters from the table that contains the id (lower aggregation than I want).

Hi Filip, please let us check this and we will get back to you with more details as soon as possible.

Hi Filip, is the picture above representation of your GoodData Logical Data Model? And if yes then is the dataset with ar_days connected to the one with payment_method via a standard single-arrow or via a many-to-many double-sided arrow? I believe with the standard arrow, the

AVG(ar_days)

should give you the result from the left table. The system is trying to maintain the fact granularity and do not multiply it. But it then won’t allow you to slice this value by i.e. payment method (unless there is the many-to-many edge or you tell the system you are OK to expand the value with something like

SELECT AVG(ar_days) BY ALL payment_method

. As far as I know it should only multiply if there is a many-to-many arrow (double-sided) or if you explicitly do this “lifting” with a

BY

keyword like:

SELECT AVG( SELECT ar_days BY id)

- Then I would expect this metric to give you the results from your right (expanded) table. Could you please help me understand how does the logical data model look like and what is the resulting report you are trying to achieve (what you want to calculate and by what you want to filter/view it)? Thank you.

You're right its a m-n relationship like almost all of my table joins (this is because I want users to be able to apply filters from many tables at the same time, and not auto filter out the needed fields). This seems to have worked:

SELECT SUM(
(SELECT SUM({fact/ardays}) by {label/parent_id})
/
(SELECT COUNT({label/parent_id})))