Variable Dates (User Defined) and Status Determination
Hello there.
Hoping to find a solution to an issue I am trying to solve.
Summary
I have a series of time entry compliance dashboards. Data is pulled into Domo via the timesheet app connector.
My dashboards highlight compliance, based on 7 hours a work day, categorizing into "Full" or "Partial" or "Zero"
The solution I have works great currently, where we are only looking at the current month.
I create a new data set, and use a Group By. I create the following new fields:
TARGET HOURS:
DATE_WORKING_DIFF(TODAY()-1,(today()-DAYOFMONTH(today())))*7
TIMESHEET STATUS:
CASE WHEN ifnull((SUM(`timeSpentSeconds`)/3600),0) = 0 THEN 'Zero' WHEN ifnull((SUM(`timeSpentSeconds`)/3600),0) < (DATE_WORKING_DIFF(TODAY()-1,(today()-DAYOFMONTH(today())))*7) THEN 'Partial' ELSE 'Full' END
As our dashboards are getting more and more use, we want to enhance them.
Problem
We want to add the ability to select date ranges. Weekly, monthly, quarterly, custom.
I have those filters fine, but I want to know how to achieve the Zero/Partial/Full based on the dates the user selects on the dashboard. Right now, it's built into the table.
Here is some example data to make it easier. The reason is that there could be several rows for the same person for the same day (if they are working multiple projects).
The data on the left is what my data looks like. The data on the right is how it looks in my current process, but having user adjustable dates removes the ability for there to be a separate grouping table.
Any thoughts? I tried searching, but didn't see anything out there. Could be that I just don't know how to phrase it all.
Thanks in advance.
Answers
-
It seems like you could look at the number of distinct days and the sum of the hours and then divide it and see if it is greater than or less than 7 to determine if it is full. If you did this as a beast mode in the card, this would give you flexibility to allow the user to select different date ranges. Your beast mode might look something like this:
CASE WHEN COUNT(DISTINCT `date`) / SUM(`hours`) >= 7 'Full' WHEN WHEN COUNT(DISTINCT `date`) / SUM(`hours`) > 0 THEN 'Partial' ELSE 'Zero' END
**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.2 -
Thanks! I'll give this a try
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 688 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive