How to SUM working days correctly?
Hi everyone, I have a strange problem. Probably more of a SQL question than a Domo question, but here we go.
I have a typical date column (yyyy-mm-dd) and a column called "total working days in month" where it returns a aggregate value of the # of working days for each month. Below is an example (just assume every date is a working day):
Here's what I want to do in Domo:
- Create a Pivot Table in Domo grouped by employee
- For each employee, calculate the number of calls per working day (Calls / Total Working Days)
- Have the ability to group the data by month, quarter, and year
My Beast Mode: SUM(CASE WHERE
Activity = 'Call' THEN 1 ELSE 0 END) / SUM(DISTINCT
This works okay, but the problem is when I try to group it by anything other than month. October and September both have 21 working days, so if I want to see both October and September, it will throw off the beast mode.
How in the world do I solve this? Is it something I need to do in an ETL first?