Show Top 10 in Chart (Rank in Beast Mode)

b_rad
b_rad Contributor
in Magic ETL

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

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**
  • b_rad
    b_rad Contributor
    Answer ✓

    @GrantSmith ,

    Thanks a lot for letting me know about this alpha feature. Will try to get it enabled and see if it performs as intended.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • b_rad
    b_rad Contributor

    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!!

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**
  • b_rad
    b_rad Contributor
    Answer ✓

    @GrantSmith ,

    Thanks a lot for letting me know about this alpha feature. Will try to get it enabled and see if it performs as intended.

  • b_rad
    b_rad Contributor

    @GrantSmith ,

    Thanks a lot for letting me know about this alpha feature. Will try to get it enabled and see if it performs as intended.

  • user084115
    user084115 Member

    I would suggest to use Sort feature. Sort on Descending . Then using limit rows input 10. That should work

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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"
  • b_rad
    b_rad Contributor

    @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.

  • Jones01
    Jones01 Contributor

    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?

Categories