Beast mode calculation for aggregating data
Hi,
I have a numeric field called "days until due" which contains negative numbers for outstanding items from 1 to 100. I am trying to group these using a beast mode calc. but keep getting syntax errors. For example, when "days until due" is from 1 to 9 days I want it to be grouped as "19 days", 10 to 19 days would be "10 to 19 days". The idea is to group these and sum the value for the range and chart it.
Can anyone please suggest a beast mode calc. that works?
Cheers
Best Answer
Answers

Hi @Lashes
You can use some simple math to convert your negative number to a positive one and some trickery with the FLOOR and CEILING functions to get bands of 10.
CASE WHEN `days until due` * 1 > 60 THEN '60+ days' CASE WHEN `days until due` >= 60 THEN '60+ days' ELSE CONCAT(FLOOR(`days until due` / 10) * 10, '', (FLOOR(`days until due` / 10) * 10 + 9), ' days') END
To explain a bit further:
`days until due` / 10
Calculates a 10s bucket and inverses your negative number to a positive number
FLOOR(`days until due` / 10)
FLOOR returns a decimal without the decimal places (1.9 > 1)
We then multiply it by 10 to convert back to 10s, this will give us the lower end of the bucket.
We do the same thing for the higher end of the bucket but also add 9 to it so it'll end up being 19 instead of 10 for example.
I just chose a lower threshold of 60. If you have positives you'll need to put another condition right after the first one to bucket those  just don't multiply by 1.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 294 Workbench
 6 Cloud Amplifier
 8 Federated
 2.8K Transform
 97 SQL DataFlows
 607 Datasets
 2.1K Magic ETL
 3.8K Visualize
 2.4K Charting
 707 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
 5.3K Community Forums
 40 Getting Started
 30 Community Member Introductions
 103 Community Announcements
 4.8K Archive