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

Rvannoy
Rvannoy Member
edited March 2023 in Scheduled Reports

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)

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Rvannoy
    Rvannoy Member

    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

    image.png

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

    image.png

    56.90 is the correct total.

  • MarkSnodgrass
    MarkSnodgrass Coach

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

    image.png


    image.png


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Rvannoy
    Rvannoy Member

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


    image.png
    image.png

    Not sure why it is changing all the numbers.

  • Rvannoy
    Rvannoy Member

    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.

    image.png

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

    image.png

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

  • Rvannoy
    Rvannoy Member

    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?

    image.png
    image.png


  • GrantSmith
    GrantSmith Coach

    @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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Rvannoy
    Rvannoy Member

    @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
    MarkSnodgrass Coach

    @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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Rvannoy
    Rvannoy Member

    @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?

Categories