Excluding null values when using rank function in beast mode
I used "rank() over (order by asc)" in the beast mode, but I am getting blank rows on the top, followed by the correct rank
. Is there a way to exclude the null values when using the rank function?
Answers
-
Hi @user069636
Are you doing any sorting on your card? Would filtering out records with NULLs in your card work for you?
Are you just not wanting a rank if the value is null? If that's the case you could do something like:
CASE WHEN `field` IS NOT NULL THEN RANK() OVER (ORDER BY `field` ASC) END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Hi @GrantSmith,
With your recommendation, I was able to remove the ranking numbers for the categories that had null values. But they are still on the top of the table. I cant filter out records with null values because the aggregation isn't supported in filters.
0 -
Have you put your beast mode into the sort section of your card to sort based on the rank? That'll put the ones that are blank at the bottom (you may need to revert back to your old beast mode).
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith I have tried sorting for both the old one and the new one but they are still showing on the top.
0 -
You could add another beastmode to check for null values and filter on that. Something like
CASE WHEN `PW Sales` is NULL then 'no value' else 'value' end
You could then add this field to your filters and filter for 'value'
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
I cant use that as a filter because 'PW Sales' contains uses aggregated function. 😭
0 -
There is a beta which allows for filtering on aggregates. You can start a conversation with your CSM to see about getting into the beta.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith That's good to know. Thank you for your help!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive