Using Sum Distinct in Domobrick query
Best Answer
-
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! **0
Answers
-
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! **0 -
@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.
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! ✔️**0 -
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! **0 -
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! **0 -
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 -0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive