How do I aggregate a Beast Mode to a number rather than a case statement?

Rvannoy · 2021-12-07T22:04:26+00:00

There was an error rendering this rich post.

I have tried wrapping the statement into a Sum statement as well as a Count statement but then it just sums it or does a count. I need it to be a number.

SUM(CASE

WHEN `Column_1` = 'Value_1’

THEN SUM(`Column_1`)

END)

When I use the above it sums the numbers and I just need it to give.

(CASE

WHEN `CustomerName` LIKE'%AGRODEX INTERNATIONAL SAS%' THEN ('.14')

wHEN `CustomerName` LIKE'%ALTURISA GUATEMALA%' THEN ('.14')

WHEN `CustomerName` LIKE'%ANGLISS HONG KONG%' THEN ('3')

WHEN `CustomerName` LIKE'%BEN FOOD%' THEN ('3.20')

WHEN `CustomerName` LIKE'%BENISAND MERCHANDISING%' THEN ('5')

WHEN `CustomerName` LIKE'%BIGOS S DE RL%' THEN ('.15')

ELSE ('0') END)

Find more posts tagged with

Accepted answers

MarkSnodgrass

In your 2nd statement, don't put your quotes around your numbers, that will make them strings. Try this instead:

(CASE

WHEN `CustomerName` LIKE'%AGRODEX INTERNATIONAL SAS%' THEN .14

wHEN `CustomerName` LIKE'%ALTURISA GUATEMALA%' THEN .14

WHEN `CustomerName` LIKE'%ANGLISS HONG KONG%' THEN 3

WHEN `CustomerName` LIKE'%BEN FOOD%' THEN 3.20

WHEN `CustomerName` LIKE'%BENISAND MERCHANDISING%' THEN 5

WHEN `CustomerName` LIKE'%BIGOS S DE RL%' THEN .15

ELSE 0 END)

You could put a SUM function around this if that is what you are trying to sum.

All comments

GrantSmith

Can you expand more on what you mean by you just want a number? SUM and Count will return numbers. What does the number you want represent?

If you’re just wanting the number when it represents a value then just do

CASE

WHEN `Column_1` = 'Value_1’

THEN `Column_1`

END

MarkSnodgrass

In your 2nd statement, don't put your quotes around your numbers, that will make them strings. Try this instead:

(CASE

WHEN `CustomerName` LIKE'%AGRODEX INTERNATIONAL SAS%' THEN .14

wHEN `CustomerName` LIKE'%ALTURISA GUATEMALA%' THEN .14

WHEN `CustomerName` LIKE'%ANGLISS HONG KONG%' THEN 3

WHEN `CustomerName` LIKE'%BEN FOOD%' THEN 3.20

WHEN `CustomerName` LIKE'%BENISAND MERCHANDISING%' THEN 5

WHEN `CustomerName` LIKE'%BIGOS S DE RL%' THEN .15

ELSE 0 END)

You could put a SUM function around this if that is what you are trying to sum.

Rvannoy

Taking the quotes off worked and allowed it to be a number. When I put it in a table how to I get the total to be the sum of the numbers?

The grand total is coming up as

When I export it to Excel and sum the column it is

56.90 is the correct total.

MarkSnodgrass

What aggregation is the field using? Click on the pencil icon next to the field in the card and choose Sum for aggregation.

Rvannoy

When I use Sum it sums all the numbers not just the total.

Not sure why it is changing all the numbers.

Rvannoy

When I click on the pencil and just change the format calculation to SUM it does not change the numbers but the Grand Total is off.

This is the total when I export and sum the column.

This is the sum in DOMO. Not sure why DOMO is not getting the correct Total.

Rvannoy

OK, I know what is happening but do not know how to fix it. I got it to sum the numbers but it is only using the number once. Example is if there are 3 customers with a distinct number of 1.5 it only uses the 1.5 once not 3 times.

The below is an example of how it is summing. The highlighted are duplicates of the the number so it is only summing using the number once. The second example is what it should be (56.90) a sum of all the numbers.

How do I get it to recognize all the numbers? It is a beast mode but I took out the parentheses and quotes string as suggested above. That gave me the what I needed as far as it being a number. I also have the aggregation set to Sum which works except for the fact that it does not sum all the numbers.

Any suggestions on how to get it to sum using all the numbers?

GrantSmith

@Rvannoy

Are you calculating the grand total yourself in your data / beast mode or do you have the total row on your table enabled in Domo? If you're calculating it yourself it's likely when you're aggregating your total you're not using the same level of granularity / you're not grouping by the same columns as your table so you're getting different results.

Rvannoy

@GrantSmith I have the total row on the table enabled. The grand total is calculating as 43.70 and it should be 56.60. It is not totaling all the numbers.

This is an example of my beast mode:

SUM(DISTINCT(CASE

WHEN `CustomerName` LIKE'%VICTUS%' THEN .08

WHEN `CustomerName` LIKE'%WENPHIL%' THEN .15

WHEN `CustomerName` LIKE'%WISYNCO GROUP LIMITED%' THEN .20

WHEN `CustomerName` LIKE'%YANTAI JIA JIA%' THEN .50

WHEN `CustomerName` LIKE'%ZENITH FOODS%' THEN 5.25

ELSE 0 END))

MarkSnodgrass

@Rvannoy the reason it is not calculating all of the numbers is because you are using SUM(DISTINCT()). Just use SUM, so it would look like this:

SUM(CASE
WHEN `CustomerName` LIKE'%VICTUS%' THEN .08
WHEN `CustomerName` LIKE'%WENPHIL%' THEN .15
WHEN `CustomerName` LIKE'%WISYNCO GROUP LIMITED%' THEN .20
WHEN `CustomerName` LIKE'%YANTAI JIA JIA%' THEN .50
WHEN `CustomerName` LIKE'%ZENITH FOODS%' THEN 5.25
ELSE 0 END)

This should total correctly for you.

Rvannoy

@MarkSnodgrass when I use sum without distinct i sums all the all the numbers. The number should be 1 but if 2 customers have a allocation of 1 it shows as 2 . I used distinct so it would show each distinct customers allocation.

Is there a way to use distinct and have the grand total on the table sum all the numbers?