Variable Dates (User Defined) and Status Determination

Shawn_S

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.

MarkSnodgrass

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