SUM(DISTINCT) or other function?

PJG

Hi gang! I've done a mockup here to try and explain what's going on with my dataset and what I want to achieve.

The first table below shows my dataset. We don't need the month column for this, but I wanted to include it to explain why each project has multiple rows in the dataset.

Essentially, I want to SUM the PlannedCost for each project and compare it to the SUM of the ForecastCost for each project. If the total Forecast is less than total Planned for each project, it's Under Budget, and the second and third tables below show this. I am displaying this as a Vertical bar chart, so in this case, it would show 2 projects Under Budget and 1 project over Budget.

I initially tried just SUM, then SUM(DISTINCT), then I've added in the FIXED piece. Still no success. My code is below. Vs my initial code, one issue now is I have no COUNT option on the Bar value within Analyzer.

case

when SUM(SUM(`ForecastCost`) FIXED (BY`ProjectName`)) < SUM(SUM(`PlannedCost`) FIXED (BY`ProjectName`)) then 'Under Budget'

else 'Over Budget'

End

Where am I going wrong? I'm not a coder or DOMO expert, so any help is greatly appreciated.

ArborRose

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:

ArborRose

Correct, you see only what is produced by the Group By. But, you can either add fields to the group (if they match that "level"). Or, you can spawn a branch off that backbone, do something on the side branch, and then use a join to join new things back to your backbone.

Edit…I said that slightly wrong. If you add a new field to that formula tile, the new field will also be part of the output.

ArborRose

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:

ArborRose

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:

PJG

Hi Arbor, thanks for the reply and really appreciate you taking the time to explain that step by step. Unfortunately, I don't have access to run things in ETL at the moment; something I'm working on resolving.

ArborRose

https://community-forums.domo.com/main/discussion/comment/97480#Comment_97480

Hmm. You could try doing it on a blank brick. In a brick you can access the datasets and make your own logic.

ArborRose

Here's the shared link to the example I have created on my freemium site:

<iframe src="https://embed.domo.com/cards/W7x0g" width="600" height="600" marginheight="0" marginwidth="0" frameborder="0"></iframe>

To replicate this, go to AppStore and find Blank Brick. Then hit get. It will set it up with Example Data. You can set this to your data and then modify the code to use the dataset instead of my coded data.

There will be three areas to paste code: JavaScript, HTML, and CSS. Here's the code to each:

HTML:

<div id="tableContainer">

    <h1>Project Budget Status</h1>

    <table id="budgetTable">

        <thead>

            <tr>

                <th>Project Name</th>

                <th>Planned Cost</th>

                <th>Forecast Cost</th>

                <th>Status</th>

            </tr>

        </thead>

        <tbody></tbody>

    </table>

</div>

CSS:

#tableContainer {

    margin: 20px;

}


table {

    width: 100%;

    border-collapse: collapse;

}


th, td {

    border: 1px solid #ddd;

    padding: 8px;

}


th {

    background-color: #f2f2f2;

    text-align: left;

}


td {

    text-align: center;

}


.status-over {

    color: red;

}


.status-under {

    color: green;

}

And JavaScript:

// Sample data

let data = [

    { "ProjectName": "Project1", "Month": "2021-10-01T00:00:00", "PlannedCost": 10, "ForecastCost": 10 },

    { "ProjectName": "Project1", "Month": "2021-11-01T00:00:00", "PlannedCost": 15, "ForecastCost": 10 },

    { "ProjectName": "Project1", "Month": "2021-12-01T00:00:00", "PlannedCost": 3, "ForecastCost": 20 },

    { "ProjectName": "Project1", "Month": "2022-01-01T00:00:00", "PlannedCost": 6, "ForecastCost": 20 },

    { "ProjectName": "Project1", "Month": "2022-02-01T00:00:00", "PlannedCost": 10, "ForecastCost": 9 },

    { "ProjectName": "Project2", "Month": "2023-01-01T00:00:00", "PlannedCost": 30, "ForecastCost": 44 },

    { "ProjectName": "Project3", "Month": "2024-07-01T00:00:00", "PlannedCost": 5, "ForecastCost": 3 },

    { "ProjectName": "Project3", "Month": "2024-08-01T00:00:00", "PlannedCost": 6, "ForecastCost": 3 }

];


// 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);

});

ArborRose

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);

});

DavidChurchman

The first table doesn't require an ETL, a fixed function or a distinct function. This is the exact kind of data Domo tends to works best with.

This BM should work:

case
when sum(PlannedCost)<sum(ForecastCost) then 'Over'
when sum(PlannedCost)>sum(ForecastCost) then 'Under'
END

However, the second table, where you get counts of over/under is harder. I would probably end up doing it with an ETL. I'm not sure it's possible to get counts across rows using just BMs, but would be curious if someone else could do it. One way to get the counts by budget is with a bar graph. Using the same BM above for budget, and COUNT(DISTINCT ProjectName):

The key is using the projectname as the series, to get it aggregate at the right level. With a lot of projects, I would probably not use data labels and color code based on budget to avoid business on the graph.

PJG

Hi all,

Thank you all so much for your help here for an ETL newbie like me. Just today, I have been given full access to ETL, so I am following the first solution from @ArborRose. I have built it exactly as described and when looking at the Output, it is giving me the result I expect! - thank you!

I do have one follow up, which is probably very basic. I needed a couple of extra columns for filtering when I added the output dataset to a card, so I've done that in the ETL with the "Select Columns" action. I added my two filtering columns as well as the three I used in Group By.

However, the two filtering attributes I added are not appearing in the Output dataset. How can add them?

Thanks!

ArborRose

If you are using the brick method, you may need to refresh or reset the dataset0. You can also make new columns with a formula tile. Say for example you set the fields for "Field1" and the formula to ''. The output tile will see that new Field1 and leave it blank.

I'm not sure how you are filtering on the extra columns. I assume you are somewhere populating those columns with values you wish to filter on.

PJG

The columns already exist in the input dataset with data in them. I just need them in the Output dataset also; no changes to them. I did refresh the data.

I can't add them in the "Add Formula" tile, as they're not available to select there; I assume I'm only seeing what is produced by "Group By"

Thanks!

ArborRose

Correct, you see only what is produced by the Group By. But, you can either add fields to the group (if they match that "level"). Or, you can spawn a branch off that backbone, do something on the side branch, and then use a join to join new things back to your backbone.

Edit…I said that slightly wrong. If you add a new field to that formula tile, the new field will also be part of the output.

PJG

I think I have finally succeeded with an extra step! I added the Join Data tile, but this added ALL the input dataset rows back in, so I then added a tile for Remove Duplicates, and it works! Thank you SO MUCH - easily the highlight of my week so far!

ArborRose

Hey, that's terrific. You could also use a filter on that branch to limit what comes through that path.