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
- 283 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 90 SQL DataFlows
- 565 Datasets
- 2K Magic ETL
- 3.4K Visualize
- 2.3K Charting
- 593 Beast Mode
- 13 App Studio
- 28 Variables
- 588 Automate
- 143 Apps
- 417 APIs & Domo Developer
- 27 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 361 Distribute
- 99 Domo Everywhere
- 260 Scheduled Reports
- 2 Software Integrations
- 96 Manage
- 93 Governance & Security
- 15 Product Releases
- Community Forums
- 37 Getting Started
- 28 Community Member Introductions
- 90 Community Announcements
- 4.8K Archive