Using Sum Distinct in Domobrick query

Options
ColinHaze
ColinHaze Member

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.

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    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:

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • ArborRose
    ArborRose Coach
    edited May 30
    Options

    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].

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ColinHaze
    ColinHaze Member
    Options

    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
    Options

    @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!

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    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:

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ColinHaze
    ColinHaze Member
    Options

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

  • ArborRose
    Options

    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • JosephMeyers
    JosephMeyers Domo Employee
    edited May 31
    Options

    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