Show Top 10 in Chart (Rank in Beast Mode)
Hi,
I am trying to build a multiline chart based on the following
Dimension: Date (x-axis), Search query (line series)
Metrics: impressions and position
The multiline chart should show the position (y-axis) by date for only the top 10 search queries based on the total impressions during a the selected time period.
I cannot do the rank in the ETL because the top 10 search queries could change based on the time period selected.
Is there a way to do a beast mode calculation that would achieve this?
Thanks.
Best Answers
-
Looking around in the Dojo, this has been a feature that has been requested in the past, but has not been implemented yet. It may be worth submitting a new idea in the ideas exchange section of the Dojo and see if it can get included.
There is a feature called Window Functions in Beast Mode that you can ask your CSM to enable that can allow for rank and window functions to be written. However, it is still not going to get you quite there. I was able to write the following in a beast to produce a rank:
RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC)
However, when I tried to wrap it in a CASE statement so that I could drop it in a filter and exclude records with a rank higher than 10, I received a message that aggregated filters are not supported. The CASE statement looked like this.
CASE WHEN RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC) <= 10 THEN 'Include' ELSE 'Exclude' END
Since you stated that you can't do this in the ETL because you need to allow for different time periods to be used, you might need to consider a different way to visualize the data. A scatter plot card can hold a lot of series fairly effectively.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Hi @b_rad
There is an Alpha feature where you can filter on aggregate functions within Domo but it doesn't always function as expected. You can talk with your CSM about possibly enabling the Aggregate Filtering feature but it's still in an Alpha state so proceed with care.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks a lot for letting me know about this alpha feature. Will try to get it enabled and see if it performs as intended.
0
Answers
-
Looking around in the Dojo, this has been a feature that has been requested in the past, but has not been implemented yet. It may be worth submitting a new idea in the ideas exchange section of the Dojo and see if it can get included.
There is a feature called Window Functions in Beast Mode that you can ask your CSM to enable that can allow for rank and window functions to be written. However, it is still not going to get you quite there. I was able to write the following in a beast to produce a rank:
RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC)
However, when I tried to wrap it in a CASE statement so that I could drop it in a filter and exclude records with a rank higher than 10, I received a message that aggregated filters are not supported. The CASE statement looked like this.
CASE WHEN RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC) <= 10 THEN 'Include' ELSE 'Exclude' END
Since you stated that you can't do this in the ETL because you need to allow for different time periods to be used, you might need to consider a different way to visualize the data. A scatter plot card can hold a lot of series fairly effectively.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Mark,
Thanks a lot for the detailed answer. This is very helpful.
Its a shame that a powerful BI tool like Domo doesn't have this feature even when a basic reporting tool like Data Studio has this functionality. I'll get the "Window function" enabled and also submit a new feature request through my CSM for this.
Thanks once again!!
0 -
Hi @b_rad
There is an Alpha feature where you can filter on aggregate functions within Domo but it doesn't always function as expected. You can talk with your CSM about possibly enabling the Aggregate Filtering feature but it's still in an Alpha state so proceed with care.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks a lot for letting me know about this alpha feature. Will try to get it enabled and see if it performs as intended.
0 -
Thanks a lot for letting me know about this alpha feature. Will try to get it enabled and see if it performs as intended.
0 -
I would suggest to use Sort feature. Sort on Descending . Then using limit rows input 10. That should work
0 -
@user084060 keep in mind that the user has dates on the axis. So if my GROUP BY clause is Date and Search Query, then the number of rows I want to keep is Number of Dates x 10.
Course ... if your number of dates fluctuates you get weird results.
I know i'm not offering solutions ... I would say Domo doesn't have a good solution for this use case.
I would preaggregate in ETL or a Dataset View (and grit my teeth angrily :P )
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
@user084115 your recommendation wont work because of two things
1) I want to rank on impressions but my chart is going to show a different metric "position".
2) The ranking should be for the "query" dimension but date would be on the x-axis.
I also reach out to Domo support and they actually echoed @MarkSnodgrass and @GrantSmith responses.
I got the window function alpha feature enabled and now able to rank the queries using Beast Mode calculation in the chart. I am still waiting for them to enable another alpha feature which would enable me to filter the chart based on the aggregated rank Bease Mode calculation.
Will update this thread once I get this working.
Thanks a lot everyone for your responses!!! It was very very helpful.
0 -
Sorry picking up this old thread. Does anyone know if this is possible now.
Line chart with dates on the x axis but I want to only show the top n (from a variable) based on the total period?
I still don't think you can get the rank based on the whole range as it is grouped by date?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive