How to SUM working days correctly?

pstrand2
pstrand2 Member
in Charting

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

Screenshot 2021-11-02 121344.png

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?

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    image.png

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @pstrand2

    Could you just add the fractions together?

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


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • pstrand2
    pstrand2 Member

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

  • pstrand2
    pstrand2 Member

    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
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    @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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • pstrand2
    pstrand2 Member

    @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:

    Screenshot 2021-11-03 075517.png

    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:

    Screenshot 2021-11-03 080130.png

    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
    MarkSnodgrass Coach

    @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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    image.png

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • pstrand2
    pstrand2 Member

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

  • GrantSmith
    GrantSmith Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories