TOP5

Hey Everyone!

I'm here again to use your brains! 😁

Our team is trying to visualize a simple TOP5 of interprise partners considering the total sales of each and we need to see that by month for the past 2 years. We already did this:

Beast mode code for ranking:

The ranking beast mode is working properly when we use a table to show, but if we change for a vertical bars layout:

We already tried with CALLDAY in the SORTING area and without it.

We just get to show that (the graphic already shown the TOP providers by month in the past 2 years, but not the only TOP5 😥):

Any ideas!?


PS.: We limited by governance and we can't use any kind of dataflow. the data isn't in DOMO DW. Our origin is a view from Snowflake.

PS2.: We did a test with a smaller dataset (uploaded in csv) and it works! The rank beast mode is practicaly the same (only the fields changed) and we could be able to filter and sort:


Tagged:

Answers

  • HowDoIDomo
    HowDoIDomo Contributor

    Hi Paulo,

    I'm not sure how to do this myself, but am very interested in learning how as well. Have you seen Making a top 5, bottom 5 filter card — Dojo Community (domo.com) or filter top 10 — Dojo Community (domo.com)?


    They may be useful for your situation. Otherwise, I'm sure one of the resident experts will come help!

  • Because how domo interprets data you need to wrap your aggregate in another aggregate - Domo will run your initial window function but then will aggregate again if you have other sorting or grouping in your card and gets confused when you only have a single aggregate (returns values for each row but can't aggregate those row level values as it's not defined and you can't select it in the column drop down like other fields because it thinks you already have an aggregate in your beast mode).


    Try SUM(SUM(1)) OVER ... instead.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @GrantSmith the rank() function should work as expected insofar as it does have the nested sum() function. so sum(sum(1)) or rank() should be interchangeable.

    If you are using Snowflake as your backend, keep in mind that Domo has to 'translate' DQL (domo query language) into Snowflake SQL equivalent. Not all functions have been implemented.


    I would be unsurprised if Domo hadn't finished implemented Rank() functions in Federated Query but check in with Support / Domo Engineering.

    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"