# How do I create a rank to dynamically control my bar chart? **URL:** \ **Category:** Q&A\ **Tags:** analysis, parameters, formulas, calculations, visuals, error, charts, visual, formatting, Business-Intelligence-Engineer, quick-sight\ **Created:** [June 4, 2025, 9:48pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922 "2025-06-04T21:48:42Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![July](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@July](https://community.amazonquick.com/u/July)\ **Post date:** [June 4, 2025, 9:48pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/1 "2025-06-04T21:48:42Z")
Hi, I have a stacked bar chart by categories. I have more than 10 categories in the base and I need to show only the top 5 and the rest I need to classify as ‘others’. I couldn’t do it by dense\_rank, nor by rank ☹ I have the category, value and date fields. I need to create a rank to show the 5 highest value categories per month. Please help me? Thank you ---
**Author:** ![lary\_andr](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/lary_andr/32/27681_2.png) [@lary\_andr](https://community.amazonquick.com/u/lary_andr)\ **Post date:** [June 4, 2025, 10:10pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/2 "2025-06-04T22:10:39Z")
Hello @July hope this message finds you well If I understand well your problem, you can utilise the `topBottomRank` aggregation function to rank the categories and then create a calculated field to group the categories not in the top 5 as “Others”. Here’s a step-by-step guide: 1. **Create a Calculated Field for Ranking:** Navigate to the calculated fields section and create a new calculated field with the following formula: 2. **Create a Calculated Field to Categorise as ‘Others’:** Next, create another calculated field to group the categories that are not in the top 5: 3. **Utilise the New Field in Your Chart:** Now, use the calculated field that groups categories as ‘Others’ on the category axis of your stacked bar chart. This should allow you to display only the top 5 categories per month and group the rest as ‘Others’. By following these steps, you should be able to effectively manage and visualise your data in QS. Please let me know if it helps you ---
**Author:** ![July](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@July](https://community.amazonquick.com/u/July)\ **Post date:** [June 5, 2025, 12:52pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/3 "2025-06-05T12:52:12Z")
Hello @lary_andr I did´nt find the “topBottomRank” in Quick Sight. The command does not exist. ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/a/8/a8da9e67737365059498f2f353c8a85b012907a1.png) ---
**Author:** ![July](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@July](https://community.amazonquick.com/u/July)\ **Post date:** [June 5, 2025, 9:26pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/4 "2025-06-05T21:26:45Z")
I tried to do it, but I recieved the error “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.” The dense\_rank is working, but the ifelse command doesn´t work. ifelse ( denseRank ( [{month} ASC, sum({value}) DESC , {liq} ASC] ,[{month}] ) \<= 5, {liq}, ‘Others’ ) thanksss ---
**Author:** ![July](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@July](https://community.amazonquick.com/u/July)\ **Post date:** [June 7, 2025, 10:59am UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/5 "2025-06-07T10:59:18Z")
Hello guys, I still really need help, I couldn’t make it work. When the field is created, it only works for one month, when I filter more than one month, the rank does not respect the month. I need to rank the highest financial value divided by month and category. I’ve been trying for a week, but without success. ---
**Author:** ![prantika\_sinha](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/prantika_sinha/32/21878_2.png) [@prantika\_sinha](https://community.amazonquick.com/u/prantika_sinha)\ **Post date:** [June 7, 2025, 7:27pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/6 "2025-06-07T19:27:13Z")
Hi @July , Try the below steps: Step 1: Calculate category level values Use sumOver to return same value across all months, since you are not looking at varying months Sample calculation : ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/1/4/14a7f8c00098804aa9f6898126a8db85b9930594.png) However if you are filtering months, you can set the calculation level as PRE\_FILTER Step 2 : Create the Bar chart Step 3: Use the Filter option to create a category filter and use top/bottom filter ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/8/1/81cb3bb4e20505c2160809cbd08fbf05d3cce4a3.png) Have created a sample solution with a dummy data accessible [here](https://community.amazonquick.com#Arena-dashboardId=f4c804be-4645-417d-ae14-d7c4cfd42c96&authorId=CU-7605). Let me know if this resolves the issue! Thanks, Prantika ---
**Author:** ![July](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@July](https://community.amazonquick.com/u/July)\ **Post date:** [June 8, 2025, 2:21am UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/7 "2025-06-08T02:21:04Z")
Hello @prantika_sinha The chart I need is stacked by category. On the X axis, I need to show the months, on the y axis I need to show the value. I need to show the 5 biggest categories/values ​​and the rest show as “others” in the same chart. Thank you ---
**Author:** ![prantika\_sinha](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/prantika_sinha/32/21878_2.png) [@prantika\_sinha](https://community.amazonquick.com/u/prantika_sinha)\ **Post date:** [June 8, 2025, 10:54am UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/8 "2025-06-08T10:54:19Z")
But since you are putting the categories across months, the top 5 is varying for each month. so filtering will not help. Is that the issue ? ---
**Author:** ![prantika\_sinha](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/prantika_sinha/32/21878_2.png) [@prantika\_sinha](https://community.amazonquick.com/u/prantika_sinha)\ **Post date:** [June 8, 2025, 12:28pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/9 "2025-06-08T12:28:50Z")
We can use the group/colour settings and set the no. of bar segments displayed as 5. Check here for sample implementation. Just a catch, instead of adding the direct field value, create a calculated field sum(value) that will allow you to sort the value field correctly. Here is a snip to the implementation, details you can check in the same published analysis [sample solution 46922](https://community.amazonquick.com#Arena-dashboardId=f4c804be-4645-417d-ae14-d7c4cfd42c96&authorId=CU-7605) ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/7/a/7a08c9c6820af9479e4e9c185ed1d71116ad5905.png) ---
**Author:** ![July](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@July](https://community.amazonquick.com/u/July)\ **Post date:** [June 8, 2025, 3:54pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/10 "2025-06-08T15:54:42Z")
No, because it can really vary. ---
**Author:** ![July](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@July](https://community.amazonquick.com/u/July)\ **Post date:** [June 8, 2025, 3:57pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/11 "2025-06-08T15:57:18Z")
@prantika_sinha Wow! So does that mean I don’t need a calculated field to rank? Is this “others” category Quick Sight itself categorized? Please show me how your calculated field “total value” was created. Thank you!! ---
**Author:** ![July](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@July](https://community.amazonquick.com/u/July)\ **Post date:** [June 9, 2025, 1:28pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/12 "2025-06-09T13:28:31Z")
It shows the 5 categories, but not the 5 biggest. ---
**Author:** ![prantika\_sinha](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/prantika_sinha/32/21878_2.png) [@prantika\_sinha](https://community.amazonquick.com/u/prantika_sinha)\ **Post date:** [June 9, 2025, 5:32pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/13 "2025-06-09T17:32:48Z")
If you sort the values in order, it should allow you to limit biggest 5. > [@prantika\_sinha](#): > > [sample solution 46922](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/9#Arena-dashboardId=f4c804be-4645-417d-ae14-d7c4cfd42c96&authorId=CU-7605) You can refer to the example shared above. You can replicate the analysis in Arena and probe in. ---
**Author:** ![July](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@July](https://community.amazonquick.com/u/July)\ **Post date:** [June 10, 2025, 9:17am UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/14 "2025-06-10T09:17:10Z")
If I sort by order, the months are out of order ---
**Author:** ![WLS-D](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-d/32/36109_2.png) [@WLS-D](https://community.amazonquick.com/u/WLS-D)\ **Post date:** [June 18, 2025, 3:34pm UTC](https://community.amazonquick.com/t/how-do-i-create-a-rank-to-dynamically-control-my-bar-chart/46922/15 "2025-06-18T15:34:24Z")
Hello @July Are you still working on this or were you able to find a solution? I think that @prantika_sinha 's example above is the closest solution but I’m not sure I totally understand the end objective. To clarify, you want a stacked bar chart with 6 segments sorted from highest to lowest, with the first 5 being actually category names and the 6th saying “Other” by month. If the above is the case, ultimately, I don’t think there is a way to control the order of the segments in the bars via sort orders in the field well. It seems to be based on alphabetical/numeric order of the field value i.e. A, B, C, D or 1, 2, 3 etc… This topic still has what I think is the best solution: > [@How do you sort stacked bar chart by value](https://community.amazonquick.com/t/how-do-you-sort-stacked-bar-chart-by-value/1319/2): > > Hi @sambhavbhandari - Currently QS will not sort the Group/Color using a value. Here is a workaround that you can use. Step 1 - Create a calculated field that will show your stacked group plus what rank the value is. c\_rank: rank([value DESC],[col2], PRE\_AGG) Step 2 - Concat your rank + your stacked group. c\_sort\_col1\_string: concat(tostring({c\_rank}),col1) Step 3 - Change the legend colors so that all stacked groups are the same color. Step 4 - Hide the legend and create your own lege…