Using Sum Distinct in Domobrick query

ColinHaze · May 30

Hi,

How would you go about fixing this error? When I remove Sum(Distinct) it works but I just want to add up the distinct values. Am I writing this wrong? The column name is called TotalProgress.

ArborRose · May 31

I'm assuming you don't have this working. I'm just now getting a chance to look back at your comments. Here's an example using EXAMPLE SALE DATA, written in the way you have your example setup.

HTML:

<!DOCTYPE html>

<html>

<head></head>

<body>

<div id="collintest"></div>

</body>

</html>

Javascript:

var domo = window.domo;
var datasets = window.datasets;

var fields = ['revenue', 'sales_rep'];
var query = `/data/v1/${datasets[0]}?fields=${fields.join()}`;
domo.get(query).then(handleResult).catch(function(error) {
  console.error("Error fetching data: ", error);
});

function handleResult(data) {
  // Aggregate revenue by sales_rep
  var aggregatedData = {};
  
  data.forEach(function(row) {
    var salesRep = row.sales_rep;
    var revenue = parseFloat(row.revenue);
    
    if (!aggregatedData[salesRep]) {
      aggregatedData[salesRep] = 0;
    }
    
    aggregatedData[salesRep] += revenue;
  });

  // Prepare the result as a string
  var result = '';
  for (var rep in aggregatedData) {
    if (aggregatedData.hasOwnProperty(rep)) {
      result += `${rep}: ${aggregatedData[rep].toFixed(2)}\n`;
    }
  }

  // Insert the result into the HTML div with id="collintest"
  document.getElementById('collintest').innerText = result;
}

The result:

ArborRose · May 30

Your code looks correct. I don't see an issue there. Perhaps there's an issue with something else. Check your column names and make sure you follow the case sensitivity, like capitalization. Verify you are on dataset[0].

ColinHaze · May 30

ArborRose

Here is my Console. It works when I remove the Sum distinct part in the query. I change nothing else in my code but adding Sum Distinct, here is what I get.

(With Sum Distinct)

(Without Sum Distinct)

david_cunningham · May 30

@ColinHaze - I'm not super familiar with Domo Bricks, but I did notice something in your query. You are using backticks to encapsulate your SUM DISTINCT, but using apostrophes for the other fields.

I would've expected the aggregation to be done in the query portion, but again, not super familiar with Bricks so take what I say with a grain of salt.

Looking at the documentation, it looks like there is a "unique" queryOperator.

https://developer.domo.com/portal/8s3y9eldnjq8d-data-api

Their example also shows apostrophes around the query portion of domo.get(), where as you have backticks.

Hope this at least can spark some inspiration or point you in the right direction!

ArborRose · May 31

I'm assuming you don't have this working. I'm just now getting a chance to look back at your comments. Here's an example using EXAMPLE SALE DATA, written in the way you have your example setup.

HTML:

<!DOCTYPE html>

<html>

<head></head>

<body>

<div id="collintest"></div>

</body>

</html>

Javascript:

var domo = window.domo;
var datasets = window.datasets;

var fields = ['revenue', 'sales_rep'];
var query = `/data/v1/${datasets[0]}?fields=${fields.join()}`;
domo.get(query).then(handleResult).catch(function(error) {
  console.error("Error fetching data: ", error);
});

function handleResult(data) {
  // Aggregate revenue by sales_rep
  var aggregatedData = {};
  
  data.forEach(function(row) {
    var salesRep = row.sales_rep;
    var revenue = parseFloat(row.revenue);
    
    if (!aggregatedData[salesRep]) {
      aggregatedData[salesRep] = 0;
    }
    
    aggregatedData[salesRep] += revenue;
  });

  // Prepare the result as a string
  var result = '';
  for (var rep in aggregatedData) {
    if (aggregatedData.hasOwnProperty(rep)) {
      result += `${rep}: ${aggregatedData[rep].toFixed(2)}\n`;
    }
  }

  // Insert the result into the HTML div with id="collintest"
  document.getElementById('collintest').innerText = result;
}

The result:

ColinHaze · May 31

ArborRose
I ended up just running it through an ETL and aggerating it there. Thank you for the insight!

ArborRose · May 31

I do the same (aggregate through an ETL), then granulate any further calculations on the javascript code. The one I'm doing right now has a dozen columns with delta over year and % of delta.

A couple formulas you may find helpful:

    const formatCurrency = (value) => '$' + value.toFixed(0).replace(/\d(?=(\d{3})+$)/g, '$&,');
    const formatPercentage = (value) => value.toFixed(2) + '%';
    const changeSign = (value) => -value;

On a brick (javascript & html), these format output as currency, %, or change the sign. Then use this javascript statement

document.getElementById('{your html id}').innerText = formatCurrency({your field value});

to populate your html.

JosephMeyers · May 31

It looks like you have a solution figured out but just chiming in to say you can't aggregate inside the fields section. Aggregates are a query param like sum=field1,field2
See - https://developer.domo.com/portal/8s3y9eldnjq8d-data-api#aggregations

Using Sum Distinct in Domobrick query

Best Answer

Answers

Categories