ETL setup - duplication breaking calculations
I'm making a dataset where the concept is to take staff's login and logout times and figure their logged in hours per day. Then, we want to know certain measures based on their hours logged in.
So, in ETL, I've gotten their total logged in hours per day to one row, grouped by the date and the staff member. Then, group the other measures (calls per day, appointments scheduled per day, etc..) by date and staff member and join on staff and day = staff and day.
This was all working great until they want to know count of specific "call outcome codes" per hour. Shown in my excel attachment, you can see how bringing in the "call outcome codes" messes up my 1 row per staff member, per day, creating multiple rows due to several different outcome code possibilities.
I don't want to pivot the outcome codes, because their are a lot of them and I think this might also make it difficult for card builders.
I have tried using partitions in beastmodes along the lines of (outcome code)/ (max hours partitioned by user, day), but partition beastmodes always give me issues.
I did have one thought, but I wanted to get others' input before I change my ETL.
Since hours per day will always be my denominator, I was wondering if I could just put a row count in there in the etl
for the hours. Then, in my beastmode just say (sum of outcome code when outcome code = x) / (sum of hours when row count = '1')
- 7.3K All Categories
- 13 Getting Started in the Community
- 142 Beastmode & Analytics
- 1.8K Data Platform & Data Science
- 54 Domo Everywhere
- 2K Charting
- 1K Ideas Exchange
- 904 Connectors
- 237 Workbench
- 342 APIs
- 77 Apps
- 19 Governance & Productivity
- 235 Use Cases & Best Practices
- 50 News
- 473 Onboarding
- 573 日本支部