Date Diff beast mode calculation with multiple variables
Hi, I am trying to write a beast mode that provides the number of days between when a file was received and when it was completed, if the file was received after the expiration date then date received is used to calculate the number of days, if the file was received before the expiration date then the expiration date is used to calculate the number of days. The logic on this was pretty simple, but I have run into an issue when another variable comes into play, occasionally we will have a file that has an expiration date after the date completed so for example, a file will have a date received of 1/2/2020 a date completed of 1/15/2020 and then an expiration date of 1/5/2021, so with the logic that is currently set up on the beast mode it will use the number of days between the expiration date and date completed because the expiration date is after the date received, the problem with this is that in the case where the expiration date is after the date completed I receive a negative day count which throws off all of my averages. So in the example I provided the number of days should be 13, but instead it is -355.
The logic that needs to be added basically needs to say if the expiration date is after the date completed then use date received instead of expiration date. Below is the logic that I am already using, which is working correctly, I just need that last piece added and I am having some trouble.
avg(DATEDIFF(`DateCompleted`,case when datediff(`Date Received`,`Expiration Date`) > 0 then `Date Received` else `Expiration Date` end))
Best Answer
-
Never-mind I figured this out.
0
Answers
-
Never-mind I figured this out.
0 -
depending on your data COALESCE / IFNULL could work, but most likely a simple CASE statement would be an easy way forward.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 305 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 106 SQL DataFlows
- 646 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 774 Beast Mode
- 75 App Studio
- 43 Variables
- 731 Automate
- 186 Apps
- 470 APIs & Domo Developer
- 61 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 403 Distribute
- 117 Domo Everywhere
- 277 Scheduled Reports
- 9 Software Integrations
- 136 Manage
- 133 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive