Date Diff - Tenure
Hello,
I am looking to build a tenure-based view for a list of employees. I have a hire date field and I want to create a filter to show some categorized options.
Can someone validate if this is possible and any ideas will appreciated
Employees that have 3 weeks of experience ( Something like if datediff today()- HireDate) >21 then 'Employees with three weeks experience' and so on?
Best Answer
-
@gospel That is correct. You could also do the opposite if that is easier for you if you reverse your signs like this:
CASE WHEN DATEDIFF(CURRENT_DATE(), `dt`) <= 7 THEN '1 week or less' WHEN DATEDIFF(CURRENT_DATE(), `dt`) <= 14 THEN '2 Weeks' WHEN DATEDIFF(CURRENT_DATE(), `dt`) <= 21 THEN '3 Weeks' ELSE 'More than 3 weeks' END
You can create as many WHEN statements as you want and then use the ELSE statement to do anything beyond that.
**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.1
Answers
-
Yes, you could do this:
CASE WHEN DATEDIFF(CURRENT_DATE(), `dt`) >= 21 THEN '3 Weeks or more' WHEN DATEDIFF(CURRENT_DATE(), `dt`) >= 14 THEN '2 Weeks or more' .... END
You will want to work chronologically, though in order to make it work. In this case going from greatest to smallest since the case statements stop evaluating and exit out once they find a match.
**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.1 -
Thank you @MarkSnodgrass , So start with the highest bucket like More than three months, then slide down to the cases with weeks.
0 -
@gospel That is correct. You could also do the opposite if that is easier for you if you reverse your signs like this:
CASE WHEN DATEDIFF(CURRENT_DATE(), `dt`) <= 7 THEN '1 week or less' WHEN DATEDIFF(CURRENT_DATE(), `dt`) <= 14 THEN '2 Weeks' WHEN DATEDIFF(CURRENT_DATE(), `dt`) <= 21 THEN '3 Weeks' ELSE 'More than 3 weeks' END
You can create as many WHEN statements as you want and then use the ELSE statement to do anything beyond that.
**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.1 -
Alternatively you could utilize some math functions to simplify your beast mode to calculate just the tenure in weeks - this would allow easier filtering using a single numeric value:
FLOOR(DATEDIFF(CURRENT_DATE(), `dt`) / 7)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
Thank you both @GrantSmith and @MarkSnodgrass , am all set.
1 -
Glad to hear it @gospel . If you can mark any of the answers as accepted that you feel helped you, it will help out the rest of the community.
**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
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 96 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 705 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
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive