Count Distinct with Fixed?

Do the fixed functions actually work with Count Distinct? I'm trying to leverage them to ignore some filters, but they're not producing the right results (always significantly higher). The example below does not include the filter bit since I can't even get the base of the function to work correctly. The window function I tried below works as I would expect, but I don't think it would allow me to ignore filters.
FIXED FUNCTION
MAX(COUNT(DISTINCT(`Lead ID`)) FIXED (BY `Line of Business`))
WINDOW FUNCTION
MAX(COUNT(DISTINCT `Lead ID`)) over (partition by `Line of Business`)
Best Answer
-
@Domomon DISTINCT doesn't work with window functions, and I believe that's no exclusive to Domo, as I think that goes for MySQL and SQL Server as well. I've seen that one solution is to use DENSE_RANK() instead of COUNT(DISTINCT ), but that does take nulls into account, so just keep that in mind. If that doesn't work, you'll need to use an ETL and use a GROUP BY and joins.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
3
Answers
-
@Domomon DISTINCT doesn't work with window functions, and I believe that's no exclusive to Domo, as I think that goes for MySQL and SQL Server as well. I've seen that one solution is to use DENSE_RANK() instead of COUNT(DISTINCT ), but that does take nulls into account, so just keep that in mind. If that doesn't work, you'll need to use an ETL and use a GROUP BY and joins.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
3
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 657 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 81 App Studio
- 45 Variables
- 775 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 81 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive