When choosing a date subset the displayed value of my application is the lastest value as opposed to the sum on that subset.

In the example, 943.974 is the dosage for June 12th. The sum of dosage from June 10th to June 12th is what I need. That is 6306 in this case.

enter image description here

In my data I have:

Geom     Dosage     Date
123123    4132      6/10/2018  
123123    1231      6/11/2018  
123123     943      6/12/2018  
342523    321       6/10/2018  
342523    645       6/11/2018  
342523    342       6/12/2018  
...      ...        ...

CARTO Builder popups return the last row in your table. That's how CARTO renders a PostgreSQL table. You need to follow this long but very useful workaround to get the sum of all your overlapping polygons:

  1. Add your working layer twice onto your map. One would be the top layer and the other the base layer. You will understand this as you move to the steps.
  2. The top layer needs to have only unique geometries. In order to achieve this, apply a query like this one:


  1. Before working with the base layer, add as many widgets as you want. For instance, add a time series widget as you mention in your question.

  2. Then, apply a Create Centroids from Geometries analysis to the base layer, use SUM(dosage) as the aggregation operation and the geometry as the category. You will end up with a point layer with unique geometries and aggregated values. If you filter by any widget, you will see that the values are recalculated.

  3. Finally, add an Intersect and Aggregate analysis to the top layer. Select on the analysis parameters the base layer as the one you want to intersect with and value as the aggregated operation.

Now every time that you filter by any widget pointing to your original base layer data source, the centroid layer will be filtered and new values would be calculated and at the same time they will intersect with the top layer and you will be able to get the sum of all your filtered and intersected polygons.

  • This is amazing! thanks @ramiroaznar. I followed line by line without understanding the details and it worked like a charm. Now I really want to think it through – italo May 1 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.