Best Of
Re: SUM(DISTINCT) or other function?
If you recreate my example, and set dataset0 to your dataset, the following JavaScript code should be able to pull that data. JavaScript is case sensitive. Make sure you modify fields to exactly match the spelling and case of your own.
// Assume dataset0 is already set to your dataset
let data = dataset0;
// Aggregate PlannedCost and ForecastCost by ProjectName
let summary = data.reduce((acc, row) => {
if (!acc[row.ProjectName]) {
acc[row.ProjectName] = { PlannedCost: 0, ForecastCost: 0 };
}
acc[row.ProjectName].PlannedCost += row.PlannedCost;
acc[row.ProjectName].ForecastCost += row.ForecastCost;
return acc;
}, {});
let result = Object.keys(summary).map(projectName => {
let planned = summary[projectName].PlannedCost;
let forecast = summary[projectName].ForecastCost;
return {
ProjectName: projectName,
PlannedCost: planned,
ForecastCost: forecast,
Status: forecast < planned ? "Under Budget" : "Over Budget"
};
});
// Populate the table
let tbody = document.querySelector('#budgetTable tbody');
result.forEach(project => {
let row = document.createElement('tr');
row.innerHTML = `
<td>${project.ProjectName}</td>
<td>${project.PlannedCost}</td>
<td>${project.ForecastCost}</td>
<td class="${project.Status === 'Under Budget' ? 'status-under' : 'status-over'}">${project.Status}</td>
`;
tbody.appendChild(row);
});
Re: SUM(DISTINCT) or other function?
Sorry, that did not format well. I created a sample table based on your question (mysample):
ProjectName,Month,PlannedCost,ForecastCost
Project1,2021-10-01T00:00:00,10,10
Project1,2021-11-01T00:00:00,15,10
Project1,2021-12-01T00:00:00,3,20
Project1,2022-01-01T00:00:00,6,20
Project1,2022-02-01T00:00:00,10,9
Project2,2023-01-01T00:00:00,30,44
Project3,2024-07-01T00:00:00,5,3
Project3,2024-08-01T00:00:00,6,3
Then I created a Magic ETL:
The output can then be placed on a card:
Re: SUM(DISTINCT) or other function?
Using mysample as:
ProjectName | Month | PlannedCost | ForecastCost |
|---|---|---|---|
Project1 | 2021-10-01T00:00:00 | 10 | 10 |
Project1 | 2021-11-01T00:00:00 | 15 | 10 |
Project1 | 2021-12-01T00:00:00 | 3 | 20 |
Project1 | 2022-01-01T00:00:00 | 6 | 20 |
Project1 | 2022-02-01T00:00:00 | 10 | 9 |
Project2 | 2023-01-01T00:00:00 | 30 | 44 |
Project3 | 2024-07-01T00:00:00 | 5 | 3 |
Project3 | 2024-08-01T00:00:00 | 6 | 3 |
Create a Magic ETL:
July Community Highlights 🌟
Hello Domo Community!
It's time to recap the Community Forums most engaged posts and users in July. Follow the discussion, ideas, and user links to contribute to June's hot content 🔥
Leaderboard Top 3
We love seeing the monthly competition. Huge thank you to @ArborRose, @GrantSmith, @ColemenWilson!
- @ArborRose 772 points
- @GrantSmith 392 points
- @ColemenWilson 383 points
Top 3 Posts
Of the 303 discussions that were had in April, these three got a lot of views and engagement. Follow the links and leave a reaction if you found them helpful too.
Shoutout to the following community members for asking and answering our top discussions this month: @Bishwa @ArborRose @ColemenWilson @Abe @nmizzell @KimHeinemann1113 @MarkSnodgrass @GrantSmith
Top 3 Ideas
The Domo product is always improving because of your ideas. Of the 60 ideas posted in May, these three got the most votes. Click the links below and give these ideas additional upvotes to increase their chances of getting implemented into Domo.
- Dynamic Conditional Formatting
- Enable Drilling on Table View
- Toggle Auto Preview on/off at the instance level
Congrats to these community members for coming up with innovative ideas: @Craig_Lynch @ggenovese @akeating
New Rank-up
Congrats to @Craig_Lynch for ranking up to Contributor!
July Community Member of the Month
@nmizzell has jumped into multiple discussions and helped many users this month. @nmizzell has earned 82 points in July. We're so happy to see another face sharing their knowledge with the Domo Community. Thank you @nmizzell!
Like this post if you've been enjoying the monthly Community highlights. And make sure to congratulate @nmizzell in the comments below 🥳
Re: Domo Documentation
what if i posited the challenge to you / domo to give us a tool that allows us to document a data pipeline
what might the final functionality look like?
- generate a (set of) markdown or html document that provides a summary of the pipeline so that we could integrate that documentation with whatever 3rd party enterprise tools (alation / confluence) tools
features:
- allows us to create summaries of specific features of the pipeline
- ex. if certification is enabled on a dataset, we can provide a description of what that certification means and it's included in the documentation
- if we enable / disable pdp on datasets, we can provide an overview of how it works and optionally apply include it in our documentation
- ex. "DASH_dataset" PDP policy is based on region and allows our sales directors ...
- DWH_dataset does not include PDP because ...
- documentation configuration takes all the data currently available in the data lineage screen and allows us to add notes (and which can then be pretty printed in a nice document).
- imagine being able to roll up the functionality of datalineage diagram into a flowchart (MIRO or LucidChart with all the notes you usually spend hours agonzing to collate for the major domo.
- is configurable in that we can choose which datsets and dataflows appear in the documentation (b/c documentation for different audiences may need different levels of specificity
- don't need to include DIM_number_generator or DIM_date
- documentation can capture update schedule and ingest method (REPLACE, APPEND, UPSERT) and include settings like identity columns or sample partition keys and notes.
Integration into some management tools
Day 1 necessity for sure is the ability to documentDay 2 ask would be to include the ability to tag everything included in this pipeline so it shows up in the future workspaces product. or we can eventually write a script that says "delete everything without a project tag"Day 3 ask would be that the lineage / documentation tool can span publish / subscriber instances. with control over level of detail. For PA documenting all 3000 syndicated subscribers may be less interesting, but for use cases where customers have 3 or 100 instances, keeping track of where assets are published may be less obvious and more interesting.
- our users work in departmental instances. each department starts in a _DEV or _SANDBOX environment before publishing to a PROD instance. They may have a _QA instance before content goes to a distribution hub (playstation.domo.com) and some content ends up in playstation-mobile.domo.com.
- Data lineage is good, but beautiful documentation would be greater!
Why do we need this?
We are currently migrating 300 dashboards. Each dashboard consists of N datasets. It would be ideal to be able to have auto generated documentation so that
P.P.S. I write all this b/c i know you're making efforts to integrate tiles and hopefully tiles with Notes. ETL documentation is very granular, but the big dream is how does all of that translate into documentation that spans an entire data pipeline.
AND FROM THERE. How can domo leverage tagging, and knowledge graphs, contextualized by the words fed in via documentation to produce a recommendation system that does not necessarily need an LLM 🙂 to improve data discovery?
Re: How can I get additional Groups in this Pivot Table?
Hi @haipham,
I'm not sure how closely this translates to this particular use case, but I can generally explain how to handle the grouping logic in a similar scenario.
My general recommendation would be to split the data between forecast and actual data so they are calculated separately. Then I would use an Add Constants ETL tile where the new column being created is named "DataSet" and the values would be either Forecast, Actual, or Variance depending on which branch of the dataflow is performing what calculation. Then, I would ensure the Spend, Revenue, and ROAS columns all have the same column header naming. Then, I would append the datasets to each other and have that be my final output dataset. For the sake of argument, the columns in your output dataset could look like the following: Marketing Type, Date, DataSet, Spend, Revenue, ROAS.
Then at the card level, you would simply need to place the Marketing Type column in the "Rows" section, the Date and DataSet columns in the "Columns" section, and the Spend and Revenue and ROAS columns in the "Values" section. As an additional note, I would make sure the Date column is placed above the DataSet column after saving & closing the card. If you do it the other way around like screenshot suggests in the example above, you lose the native "Graph By" functionality of the pivot table card, and you would need to hard code MONTHNAME(`Date`) in the dataflow or a Beast Mode calculation, which can't be used dynamically.
Re: Who wants early access to a bigger Magic ETL canvas?
Love this, I've been telling folks about it all week!
Re: Domo Group Help: Navigating to Score Components in Domo
I would start with the documentation for variables, which does have a video:
The variables controls are similar to filters, in that they enable users to manipulate how the data is presented. They are different in that they are not changing which rows of data are being presented, but in my example, in what columns are being presented, or how the data is grouped. (You could tie variables or BeastModes that do affect which rows are displayed, but I'm not doing that in the example above).
The approach to this would be to create two variables, let's say Metric Variable and Grouping Variable. The options for the Metrics Variable would be Listening, Speaking, Reading. The options for the Grouping Variable would Entity and Grade.
Then you also need two BeastModes that use those variables. Let's call them Metric and Group. Metric could be:
CASE
WHEN Metric Variable = 'Listening' then Listening
WHEN Metric Variable = 'Reading' then Reading
WHEN Metric Variable = 'Speaking' then Speaking
END
Group could be
CASE
WHEN Grouping Variable = 'Entity' then Entity
when Grouping Variable = 'Grade' then Grade
END
Note that in the BeastModes, you are checking if the variable is equal to a string (single quotes), and if so, then returning a column (back ticks).
Use Metric as your value axis and Group as your category axis.
Re: Compare to Industry Trends
I think the best way to do that would be to do a dual line period over period graph Ik you said thats not what you want but hear me out. You arent comparing this period to last period, you are comparing your this period over last period to their this period over last period. So it effectively becomes your gross change/month vs their gross change/month. That way you are both on the same scale. So instead of your line being 100 150 175 and theirs is 50000 60000 70000. It would be 50% increase 16% increase vs their 20% increase 16% increase. This would effectively show your growth rate vs their growth rate.
If you insist on using those numbers the best way would be a log scale; however, I find log scales unintuitive to most ppl. Like yes they get it as a concept, but they still see how much higher the line is and that does subconsciously affect the way they are going to view it.
Re: Compare to Industry Trends
You could normalize the data:
Or just divide by a thousand, or ten thousand and then use a chart type that allows for multiple axis labels (left and right) and be sure to label the axis that has been adjusted to read "In thousands" for example.
