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 toany 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
-
It looks like the suggested solution will solve my problem. Thank you for the suggestion.
0 -
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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Categories
- 7.7K All Categories
- 12 Connect
- 925 Connectors
- 247 Workbench
- 441 Transform
- 1.7K Magic ETL
- 61 SQL DataFlows
- 456 Datasets
- 88 Visualize
- 222 Beast Mode
- 2.1K Charting
- 8 Variables
- 5 Automate
- 349 APIs & Domo Developer
- 85 Apps
- Workflows
- 17 Predict
- 3 Jupyter Workspaces
- 14 R & Python Tiles
- 242 Distribute
- 60 Domo Everywhere
- 241 Scheduled Reports
- 18 Manage
- 39 Governance & Security
- 46 Product Ideas
- 1.1K Ideas Exchange
- 6 Community Forums
- 19 Getting Started
- 6 Community Member Introductions
- 50 Community News
- 18 Event Recordings
- 577 日本支部