Using IN Beast Mode for multiple matches won't SUM

Randyb
Randyb Member
in Magic ETL

I've got a Beast Mode I built and it calculates as expected except that instead of returning the sum value of all matches it returns a row for each match.

Here is the Beast Mode:

 

SUM(Case when `title`IN ('Metal Technician','Refinish Technician','Frame Technician','Prepper','Mechanic')
then
(Case when YEARWEEK(`hireDate`)<=YEARWEEK(CURDATE()) And `dateOfTermination`is null
Then 1 Else 0
End)
End)

 

Here is an example of what gets returned, it should have a single Row showing the value of 10, not multiple Row. Any help would be appreciated.

Thanks

Randy

Picture1.png

Best Answer

  • Randyb
    Randyb Member
    Answer ✓

    Ok, I figured it out, now feeling a little on the dumb side, I had a sort order in there that I wasn't paying attention to that was based on termination date. As soon as I removed the sort order i got my single row as I was expecting all along.

    Thanks for you help.

    Randy

Answers

  • Gimli
    Gimli Contributor

    Hello @Randyb, 

    There is a good chance the card can be being grouped by something else. It looks like there is another column in the screenshot. Is this column being aggregated? In addition, any column being used in the sort will group the data. 
    Can we get a full screenshot of the card with all the columns that are being used? 

    If you don't feel comfortable sharing this through the Dojo you can always reach out to the Domo Support team by going to support.domo.com. 

    Cheers!

    **Say “Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Randyb
    Randyb Member

    The two columns were nothing more than two different versions of the Beast Mode calculation. It doesn't change anything if I have either one or both of the coumns, I still end up with a value for each employee. That is what its showing, there are 18 rows and there are 18 employees at that particular location.

    So it is showing me a row for each employee and if that employee matches the criteria in the Beast Mode then it displays a value.

    Hopefully that explains it a bit better.

    I just need to figure out a way to get it to show a single line with the summary values.

    I have a number of other columns in the dataset such as employee numbers etc but they have all resulted in the same result as single line for each.

    Let me know if there is any other information I can give you.

    Thanks

    Randy

  • Randyb
    Randyb Member
    Answer ✓

    Ok, I figured it out, now feeling a little on the dumb side, I had a sort order in there that I wasn't paying attention to that was based on termination date. As soon as I removed the sort order i got my single row as I was expecting all along.

    Thanks for you help.

    Randy

Categories