How to SUM working days correctly?

pstrand2

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 TotalWorkingDaysinMonth )

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?

MarkSnodgrass

That is correct @jaeW_at_Onyx , but can be accomplished by getting a distinct list of employees and add another join in the process. Your denominator suggestion would require the same work. It would look something like this.

The Domo Calendar data would be on the left side of the left joins and your 2nd join would need to join on date and employee. The IsWorkingDay would be done in the formula tile and would look like this:

CASE WHEN DAYOFWEEK(`dt`) = 1 THEN 0 /*Sunday*/
WHEN DAYOFWEEK(`dt`) = 7 THEN 0 /*Saturday*/
ELSE 1
END

You would do another function for your call activity. Then it makes summing very easy in your card.

GrantSmith

Hi @pstrand2

Could you just add the fractions together?

SUM(CASE WHEN `Activity` = 'Call' THEN 1 ELSE 0 END / `Total Working Days in Month`)

MarkSnodgrass

Are you just grouping by using the date range filter? If you add the month or the quarter to the actual display of the pivot table, it should calculate better.

pstrand2

Hey @MarkSnodgrass , I have the Activity Date in Columns and use the Domo Date Range to show "All Time" and Graph by "Month"

pstrand2

Hi @GrantSmith ,

I tried that, but it's adding all of the rows together.

Using the above example, if I want to see everything for October and September, the total number of working days is 210 (21 x 10 rows)

MarkSnodgrass

I think this can be done much cleaner if you do some of this work in Magic ETL. You can use the Domo Date Dimensions dataset to create a row for every day of the year and then join it to your data. Then you can create an integer field called IsWorkingDay with a value of 1 or 0. You can use the DayOfWeek function in the formula tile to determine this. This will allow you to do a simple sum of this column as your denominator, which will flexibly adapt to your grouping of month, quarter, etc.

GrantSmith

@pstrand2

Do you have a screen shot to help me better understand your issue or your desired end state? Are you saying the total number of working days should be 21 or 210? It shouldn't be adding all of the working days together but it's adding the daily calls per working day (fractions) and adding those up.

pstrand2

@GrantSmith I want a Domo Pivot table that displays employee in the rows, and the measure "Calls/Working Days" in the columns, grouped by time period (Date).

Using the original Excel data screenshot above, if I wanted it broken up by month, it would look something like this:

The calculation divides the number of calls in a given month (Oct: 3, Sep: 7, Aug: 6, Jul: 2) by the working days in month (Oct: 21, Sep: 21, Aug: 22, Jul: 22).

If I want it broken up by quarter, it would look like this:

The calculation divides the number of calls in a given quarter (Q4: 3, Q3: 15) by the working days in quarter (Q4: 21, Q3: 65).

Breaking it up by month works fine, but it messes up on the quarterly grouping, specifically Q3 because the Beast Mode I mentioned has a SUM(DISTINCT DaysInMonth ), which reads it as 21 + 22. What I want is 21 + 22 + 22. Does that make more sense?

MarkSnodgrass

@pstrand2 I would recommend my previous suggestion of using the Domo Dimensions Connector and the calendar dataset to pull in a complete list of dates and then do a left join to your dataset in Magic ETL and use the formula tile to indicate the working vs non-working days as 0 or 1 and the calls activity as 0 or 1. This will make it very flexible for grouping by month or quarter or year.

Here's a link to the KB article on the Dimensions connector if you are unfamiliar with it.

https://domohelp.domo.com/hc/en-us/articles/360042931454-Domo-Dimensions-Connector

jaeW_at_Onyx

the cleanest method would be to think of your numerator and denominator as two different sets of fact tables which you then UNION together.

your first set of facts are just employee call transactions. (i would add a callCount = 1 so you can do a clean sum instead of a count)

then your denominator should be EITHER 1 row per day of the month per employee for when they were working and a binary column (isWorkingDay) OR your denominator could be one row per month per employee with the count of working days in that month. I prefer the former b/c it gives you flexibility if you want to see the data per week.

it seems different than the use case and solution here: https://www.youtube.com/watch?v=PVbOeLSae9o&t=252s but they are actually exactly the same concept.

@MarkSnodgrass the reason why a LEFT JOIN wouldn't quite work is b/c on days that don't exist in your call data (Sat and Sunday) the employee field would be blank. so when i wanted to see my ratio grouped by employeee i still wouldn't have those sat and sunday populated. for this use case since we're only interested in weekdays you might think, "well that's fine." but if I didn't work on Friday for wahtever reason, that'd still be excluded from my data.

MarkSnodgrass

That is correct @jaeW_at_Onyx , but can be accomplished by getting a distinct list of employees and add another join in the process. Your denominator suggestion would require the same work. It would look something like this.

The Domo Calendar data would be on the left side of the left joins and your 2nd join would need to join on date and employee. The IsWorkingDay would be done in the formula tile and would look like this:

CASE WHEN DAYOFWEEK(`dt`) = 1 THEN 0 /*Sunday*/
WHEN DAYOFWEEK(`dt`) = 7 THEN 0 /*Saturday*/
ELSE 1
END

You would do another function for your call activity. Then it makes summing very easy in your card.

pstrand2

Thank you everyone! I went with @MarkSnodgrass solution and it seemed to work great. Thank you!

GrantSmith

Glad you got it working, make sure to accept his answer so it's easier for others to find the solution in the future.