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 🟑

    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!

  • GrantSmith
    GrantSmith Indiana πŸ”΄

    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.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

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