Beast mode - Age buckets / grouping
Hello everyone,
I am having trouble with a beast mode grouping outstanding records by age bucket. I am looking to calculate the number of days a record has open and then place the record within a range. Below you will find my existing beast mode which is an "Invalid formula". If someone could assist with the formula validation or suggest enhancements it would be greatly appreciated! I have reviewed many support articles related to my issue and I cannot seem to find a solution.
(CASE
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 'Greater than 3 years'
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 730 AND <1094 THEN '2-3 years '
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) >365 AND <729 THEN '1-2 years'
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) >181 AND <365 THEN 6 months - 1 year'
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 0 AND <180 THEN '0-6 Months'
WHEN `Value1` IS NULL THEN 'Unassigned'
END)
Best Answers
-
The issue is your AND statement in each sequence. You would need to re-state the DATEDIFF formula after the AND again.
You might consider re-writing it in reverse order and it would eliminate the need to have the AND statement because CASE statements exit after finding a match to the condition.
(CASE WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <180 THEN '0-6 Months' WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <365 THEN '6 months - 1 year' WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <729 THEN '1-2 years' WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <1094 THEN '2-3 years ' WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 'Greater than 3 years' ELSE 'Unassigned' END)
Hope this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
I am now having trouble sorting the series using the above beast mode. I wrote the beast mode below and added the field to the sort section. The data is not sorting properly, I am set to no aggregation and I have also tried ascending and descending options. I do not see anything in properties that would be preventing the sort.
Does anyone have any thoughts as to how I can sort this data series?
CASE
WHEN `Value1` = '0-6 Months' THEN 1
WHEN `Value1` = '6 months - 1 year' THEN 2
WHEN `Value1` = '1-2 years' THEN 3
WHEN `Value1` = '2-3 years' THEN 4
WHEN `Value1` = 'Greater than 3 years' THEN 5
ELSE 10
END0
Answers
-
The issue is your AND statement in each sequence. You would need to re-state the DATEDIFF formula after the AND again.
You might consider re-writing it in reverse order and it would eliminate the need to have the AND statement because CASE statements exit after finding a match to the condition.
(CASE WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <180 THEN '0-6 Months' WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <365 THEN '6 months - 1 year' WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <729 THEN '1-2 years' WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <1094 THEN '2-3 years ' WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 'Greater than 3 years' ELSE 'Unassigned' END)
Hope this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
It looks like the suggested solution will solve my problem. Thank you for the suggestion.
0 -
I am now having trouble sorting the series using the above beast mode. I wrote the beast mode below and added the field to the sort section. The data is not sorting properly, I am set to no aggregation and I have also tried ascending and descending options. I do not see anything in properties that would be preventing the sort.
Does anyone have any thoughts as to how I can sort this data series?
CASE
WHEN `Value1` = '0-6 Months' THEN 1
WHEN `Value1` = '6 months - 1 year' THEN 2
WHEN `Value1` = '1-2 years' THEN 3
WHEN `Value1` = '2-3 years' THEN 4
WHEN `Value1` = 'Greater than 3 years' THEN 5
ELSE 10
END0 -
You need to use your DATEDIFF evaluation in your case statement and not value1 since that is just a date. It would look like this:
(CASE WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <180 THEN 1 WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <365 THEN 2 WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <729 THEN 3 WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <1094 THEN 4 WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 5 ELSE 6 END)
Drag this beast mode function into your sort and then you should see your card display correctly.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 691 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 112 Manage
- 109 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive