Order by Trellis after limit rows in bar chart

user034162

Hey there,

 

My question is related to this request: https://dojo.domo.com/t5/Card-Building/Limiting-Pivot-Values/td-p/48031 which get a good solution from @jaeW_at_Onyx .

 

But my further question is that I want to show the data in a bar chart instead of a mega table. I put the suppliers' name on the y-axis ( it is cal 2 shown on screenshot) and put the supplier bucket as a trellis (it is cal 1 shown on screenshot). Then I use the rank function on the blue value in the beast mode to get the rank calculated field and sort it to get the top 20 suppliers. But I could not put all ranked suppliers that belong to the same bucket together. In the below ss, it is only ordered by the blue value instead of by cal 1 and blue value.

 

Here is an example of what I could get now: 

Does anyone have any suggestions on this?

 

Thank you

GrantSmith

Hi @user034162 

 

It sounds like you want the rankings within each col1 subgroup if I'm reading your post correctly. You should be able to get rankings unique to each subgroup instead of the entire dataset by adding col1 to your PARTITION clause

RANK() OVER (PARITION BY `col1`, `col2` ORDER BY SUM(`amount`))

 

user034162

 

Thanks for @GrantSmith  answer. I'd like to provide a detailed example to explain my question. Usually, the category2 that belongs to 6month in category1 would get fewer deals than category2 in the other two category1. So I could not rank within category1.

 

I give an example in the excel table chart, but I want to create a bar chart in Domo.

What I want to do is to rank category2 by ordering Deals desc and get the top 5 category2. I could get the highlighted yellow part right now in Domo in the bar chart. But I am thinking if I could change the format and let the bar chart displays like this:

They are still the same top 5 category2 but some category2 that belong to the same category1 (like A and C) could be put together by deals desc. I could get this ideal table in Domo megatable by clicking category1 desc, but I could not get this format in a bar chart type in Domo.

 

Please give me some advice if anyone knows how to do that.

 

Thank you

jaeW_at_Onyx

It's literally covered int the tutorial video ?

 

you need to calculate rank in a dataset view (or ETL) then join it on to your activity.