Using IN Beast Mode for multiple matches won't SUM
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
Best 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
0
Answers
-
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"0 -
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
0 -
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
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 96 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 705 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive