Issues with SUM Function within SQL Transformations

martinez473

Hello everyone,

I've been encountering issues with the SUM() function within my SQL transformations. I've imported large datasets and added my own calculations (as shown in the attached photos). When I try to query the three mentioned columns and group the results by month, specifically using SUM(average meals...), SUM(total reimbursable...), and SUM(participation), my participation column returns values over 100%. However, the way the calculation is written, it should return a value less than 100% for each month.

Interestingly, when I pull the raw data with the individual row calculations (without using the SUM() function), my numbers are correct. I have another dataset that I'm using to compare my numbers, and the discrepancy only arises when I query with the SUM() function.

I'm unsure if I've written my calculation incorrectly, but it seems to follow the calculations that were provided to me. Any help would be greatly appreciated! I can provide more information if needed.

MichelleH

@martinez473 It looks like the issue is that you're calculating the percentage before rolling up on a monthly level, so your card is adding up the percentages of each row of your dataset. I'd recommend changing the Meals per Day and Participation to beast modes within your card that calculate percentages based off the totals instead of individual rows.

Meals per Day

SUM(`Total Reimbursable Meals`)/sum(`Serving Days`)

Participation

(SUM(`Total Reimbursable Meals`)/sum(`Serving Days`))/NULLIF(`20th Day`,0)

MichelleH

@martinez473 Would it be possible to see an anonymized sample of your data, as well of some examples of what you're seeing versus what you're expecting?

martinez473

Hi, @MichelleH
I have attached two files, one for August 2024 and one for October 2024. I have also attached a picture of the pivot table that contains the correct numbers I am trying to match. This pivot table displays the grand total and extends to April 2025. Additionally, I have attached my Domo-created table, which goes up to March 2025. As you can see, the participation numbers are incorrect. Finally, I have attached a picture of my filtering within Domo's SQL Transformations, where I have removed unnecessary information.  

24oct.xls

aug24.xls

MichelleH

@martinez473 It looks like the issue is that you're calculating the percentage before rolling up on a monthly level, so your card is adding up the percentages of each row of your dataset. I'd recommend changing the Meals per Day and Participation to beast modes within your card that calculate percentages based off the totals instead of individual rows.

Meals per Day

SUM(`Total Reimbursable Meals`)/sum(`Serving Days`)

Participation

(SUM(`Total Reimbursable Meals`)/sum(`Serving Days`))/NULLIF(`20th Day`,0)

martinez473

@MichelleH Thank you for your help! While participation looks good for individual sites, I'm now facing an issue with adding or grouping percentages by month. I haven't found a feature to calculate these. My attempt to adjust the code with SUM(SUM(`Total Reimbursable Meals`)/SUM(`Serving Days`))/NULLIF(`20th Day`,0)) didn't work. The pivot table also didn't provide this functionality. Is there a way to aggregate data across my individual sites?