Is there a way to add a dropbox with "top 10" and filter a barchart to show only top 10 once selected?

Q&A
,
1

Is there a way to add a dropbox with “top 10” and filter a barchart to show only top 10 once selected?

I was trying here: I created a parameter “range” and the value is “top 10” and “All”
Then I created a calculated field like:

ifelse(${range} = ‘Top 10’ AND rank(${colunm_1}) DESC], [${level_1}]) <= 10, 1,
${range} = ‘All’, 1,
0)

and then I add the filter to my calculate field equals to 1, but doesn’t filter.
Any ideas?

2

Hello @igfasouza,

Based on the information you provided, I believe that method should work to filter via a custom control.

I would recommend checking the statement rank(${colunm_1}) DESC], [${level_1}]) <= 10 to ensure it is ranking and grouping the way you expect

  • It looks like there may be an extra “)” after {column_1}, but I would have expected the calculated field to throw an error
  • I personally like to duplicate the visual into a table to give another view for deep diving
    • Sometimes I will break the rank function into its own calculated field, as well, to see the ranking being applied in the table
  • Depending on how you want the filter to work and if you have duplicates, you may want to checkout denseRank instead of rank. For example: If there are duplicates, rank will return: 1,1,3,4,… whereas dense_rank will return: 1,1,2,3,…
  • Another place you may want to check is the third argument Calculation level in the rank (or denseRank) function. It defaults toPOST_AGG_FILTER. If you are expecting to rank based on a specific calculation (like count) you can apply the aggregation in the first argument sort order field (EX: count(${colunm_1}) DESC. Otherwise you may want to specify PRE_FILTER or PRE_AGG for Calculation level. Again I recommend using a table visual to see how the rank changes as you alter the arguments. Please see here for definitions of the different arguments: (rank - Amazon QuickSight)

Hope this helps!

3

Hi, thanks for the help
I put in a table and I discover that if the ${colunm_1} has the same value the rank is the same

ex:
column_1 | value | rank
example1 | 3 | 1
example2 | 2 | 2
example3 | 2 | 2
example4 | 1 | 4

so, when I look to <= 10 is not only 10 items, but all values less then 10. (that is why is not filtering)
is there a way to get only ten items?

1 Like
4

@igfasouza, that is a good question…

You could try to use the runningCount function instead of rank to act as a row_number for ${colunm_1}, but you will need to define the sort order (second argument) and partition (third argument) you desire for the top 10 (you can add more than one attribute separated by columns within the [ ] to the 2nd and 3rd arguments if needed).

For example:
runningCount(${colunm_1}, [${colunm_1} DESC], [${level_1}]) <= 10
OR
runningCount(${colunm_1}, [${colunm_1} DESC, ${level_1}] DESC, [ ]) <= 10

5

Hi, thanks for the help

I changed to runningCount and it works. Thank you

I was just wandering here: as you can see in the picture the item with rank 10 and 11 has the same value, is there a way to check that and change to show 11 instead of 10 ?
in this case is only one, but this could be more than one.
Like imagine it was show only top 7, I would like to add 8, 9, 10 and 11 as well as they have the same value.

image
image190×395 2.06 KB

6

Hi @igfasouza,
In response to asking about displaying 11 instead of 10, a couple comments earlier, Kelsey mentioned the rank function, this would provide you with numbering system that assigns the same number to duplicate count values. So that would need to be incorporated in some way.

Or, another potential work around..instead of setting up a filter to limit the running count to 10, you could setup a filter instead that only returns count values that are equal or greater than 1. Something along those lines.

Let us know if you have any additional questions, if we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

7

Hi @igfasouza,
Since we have not heard back further, I will go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!