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 toany 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
- 10.5K All Categories
- 7 Connect
- 917 Connectors
- 250 Workbench
- 466 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 173 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive