Average Count Distinct across Date Range
I am working on Turnover metrics and need to capture the average unique headcount of employees over a user selected date range.
Here is what I've tried, but Domo keeps giving errors:
COUNT_DISTINCT(CASE WHEN (Team Member status
= 'Active' OR Team Member status
= 'Unpaid Leave') AND Date
BETWEEN 'start_date' AND 'end_date' THEN Team Member ID
END) / COUNT_DISTINCT(Date
)
Does anyone else have experience with this?
Answers
-
@ncelis It looks like you're not using the correct syntax for the count distinct function. The word "Distinct" needs to be inside the parenthesis like below:
COUNT(DISTINCT CASE WHEN (Team Member status = 'Active' OR Team Member status = 'Unpaid Leave') AND Date BETWEEN 'start_date' AND 'end_date' THEN Team Member ID END) / COUNT(DISTINCT Date)
0 -
Thanks @MichelleH, but it looks like Domo is still throwing an error. Here is your calc with my specific field names, and the error message:
0 -
So I am closer to a solution using this Beast Mode:
COUNT(DISTINCT CASE
WHEN (Team Member Status
= 'Active' ORTeam Member Status
= 'Unpaid Leave')
THENTeam Member ID
END)
/
COUNT(DISTINCTDate Scaffold
)This "Average Headcount" now matches my "Active Headcount" totals on a daily basis which is great! (There are 2 lines in the viz, but are the same amounts so it appears like 1 line).
However, when I switch the visual to "Graph by" week or month, it appears the new "Average Headcount" calc is not working as intended (totals are way too low):
Any ideas on why my "Average Headcount" would be so low?
0
Categories
- All Categories
- 1.5K Product Ideas
- 1.5K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 560 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 575 Beast Mode
- 13 App Studio
- 28 Variables
- 582 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 26 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 356 Distribute
- 95 Domo Everywhere
- 259 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 89 Community Announcements
- 4.8K Archive