Hi team, I’m running into an issue with a metric that calculates peak attendance. Can someone please help me figure out how to solve it? We’re using a

SUM(... OVER (PARTITION BY ...))

window function to calculate the total people count, and we expect it to respect the building filters selected by the user. However, it appears that GoodData adds the

WHERE

clause outside of the subquery, so the result always includes data from all buildings — even when specific ones are selected. We’ve tried refining the

PARTITION BY

clause and building filters, but GoodData’s SQL wrapper seems to alter the execution order, affecting the expected partitioning logic. Has anyone else experienced this? Is there a recommended workaround to make the window function respect the building-level filters? Thanks!

Hi Suzhe, Thank you for reaching out. Could you please send us a direct link to the insight or metric in question? We would like to take a closer look since you mentioned that the final result is being changed by the SQL send by the platform to your data source. Feel free to DM me if this is more comfortable for you. I will check it once we have the access and show you how to go around it

Hi Suzhe, I see you have built an SQL dataset with a query where the window function is precomputed. The issue is that the function is calculated when the data is being fetched, and then any subsequent querying done via the UI will be done upon the already fetched data, in other words, the filters applied by the users will not be included in the window function leading to misleading or incorrect errors. By doing the filtering, the platform is simply hiding rows that have been already fetched, which by definition does not correspond to the correct outcome of window functions. At this moment, I cannot say for sure whether this is by design (since SQL dataset are meant for retrieving and cleaning up data) or if there are any improvements that can be done in this regard, but I can consult it internally. In the meantime, please consider querying the people_unique_count as a regular attribute and use MAQL for the rolling window. For example:

SELECT (RUNNING TOTAL)(...) ROWS BETWEEN (...) AND (...)
SELECT RUNAVG (...) ROWS BETWEEN (…) AND (…)