How do I create a rank to dynamically control my bar chart?

Question & Answer
, , , , , , , , ,
1

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 :frowning:

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

2

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:

    topBottomRank([Value], [Category], ["Date"], 5, 'DESC')

    This will rank the categories based on the value for each month.

  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:

    ifelse(
    topBottomRank([Value], [Category], ["Date"], 5, 'DESC') <= 5,
    [Category],
    'Others'
)

  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

3

Hello @lary_andr
I did´nt find the “topBottomRank” in Quick Sight. The command does not exist.

image
image217×336 2.65 KB

4

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

5

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.

6

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
image297×141 3.81 KB

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
image243×776 23.3 KB

Have created a sample solution with a dummy data accessible here.

Let me know if this resolves the issue!

Thanks,
Prantika

1 Like
7

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

8

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 ?

1 Like
9

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

image
image1641×506 65.9 KB

1 Like
10

No, because it can really vary.

11

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

12

It shows the 5 categories, but not the 5 biggest.

13

If you sort the values in order, it should allow you to limit biggest 5.

You can refer to the example shared above. You can replicate the analysis in Arena and probe in.

14

If I sort by order, the months are out of order

15

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:

1 Like