Beast Mode for Aging Bucket
Hi, I'm writing a beast mode to create the aging bucket, it's is showing results but it's has everything under < 15 days. Note sure what I'm doing wrong here.
The 0.06% in 15-30 Days are the negative #
(CASE WHEN ((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 15 THEN '15 Days'
WHEN ((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) > 15 AND ((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 30 THEN '15-30 Days'
WHEN `Application Started` is NULL THEN 'App Not Submitted' END)
Best Answer
-
Your days are negative because your date assigned is coming after the application started date. And because -27 is less than 15 they're all falling into the bucket. You have a few options. One is you can utilize the ABS function to convert any negatives to positive so in you cases where the difference is -27 it'll evaluate to 27 and fill the bucket.
(CASE WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 15 THEN '15 Days' WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) > 15 AND ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 30 THEN '15-30 Days' WHEN `Application Started` is NULL THEN 'App Not Submitted' END)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Answers
-
Hi @Hiraayub
This is because you have your dates swapped. Which date is supposed to come first? You need to do your end date - your start date since the end date will be greater.
Swap `Application Started` with `Date Assigned` such that `Date Assigned` is coming first.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Grant, our Data is very complicated at the moment there are a lot of manual date entries.
Date Assigned is when we assigned that case to someone to start the application so Date assigned first is right.
<15 days has all the correct data but it not creating the aging bucket < 15 and 15-30.
0 -
Your days are negative because your date assigned is coming after the application started date. And because -27 is less than 15 they're all falling into the bucket. You have a few options. One is you can utilize the ABS function to convert any negatives to positive so in you cases where the difference is -27 it'll evaluate to 27 and fill the bucket.
(CASE WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 15 THEN '15 Days' WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) > 15 AND ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 30 THEN '15-30 Days' WHEN `Application Started` is NULL THEN 'App Not Submitted' END)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
thank you it worked :)
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
- 692 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
- 113 Manage
- 110 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