Using features of CARTO Builder to dynamically calculate the average of selected columns

Question

I am trying to find a simple way to calculate the average of selected columns (avg per row) dynamically in CARTO, using only the pre implemented features of CARTO Builder so that it is as user-friendly as possible and does not require manually running SQL code for each calculation.

My underlying dataset contains several columns score1, score2, etc. I would like to compute a new column avg_selected_cols based on the user input (say columns score1, score3 and score4) that contains (score1+score3+score4)/3. Ideally, the columns should be selected through some widget to make it as easy as possible.

So far I am using a sync table from Google Drive that calculates this average with AVERAGEIF clauses based on a row where I just set an "x" for each column I want to include. Synching from Google Drive, however, can be done at most every 15 minutes but I need the map/ data to update almost instantly.

ramiroaznar · Answer

Unfortunately, CARTO Builder only supports aggregation metrics (max, min, avg, count) based on single columns. That metric is generated from the features of the (analysis) layer node that points that particular widget but also filtered by the selected categories and numerical ranges of other category and histogram widgets pointing to it.

If you do not want to have an SQL applied to that sync table, you can follow the steps on this HELP article to achieve a similar result. Basically, you need to create a stored procedure and trigger it every time the sync happens.

Using features of CARTO Builder to dynamically calculate the average of selected columns

One Answer

Add your own answers!

Ask a Question