[SOLVED] CARTO - Carto builder SQL query for map Extent and dynamic calculation

CARTO - Carto builder SQL query for map Extent and dynamic calculation

I have a builder enterprise account at carto.com

I have an SQL query for an empty layer defined as follow :

SELECT (sum(montant_periode_en_cours) OVER() - sum(montant_periode_precedente)OVER()) / 
        sum(montant_periode_precedente) OVER() as tx_croissance_moy , *
from "anagraph-clement".purvodka_master_attempt_2

It works well as it returns my all the points I need on the map with the calculation in the right field.

BUT !

The formula widget is not able to display a dynamic number based on the current map extent data.

Do you know any way to achieve that BBOX dynamic calculation on the fly ?

Thank you for your time !

Solution

That is normal because the query is wrapped to retrieve the metric based on the bounding box, but your aggregation is computed before that wrapping.

I'm afraid there's no way to achieve that using BUILDER, and I can only think on doing that manually on a custom application. In that scenario, you can use Leaflet/GMaps methods to get the current bounding box, and then using SQL API get the KPI you are asking for.