Bar Chart Sorting and Categorization

I have a horizontal bar chart with more than 100 bars. Each bar has sections based on certain categories.
I want to show only the Top 10 results in the bar chart. But when I put the limit in the dashboard the values do not come properly. It counts each section of the Bar as 1 result and shows the result.
I have also tried using the Parameter in the worksheet and passing the max value through that. But then the sections of the bar are all the same width and don’t show the correct value. Also, they can’t be sorted after that for some reason. The image below shows the graph after passing the max value through the parameter in the sheet.

Hi @arathi,

I tested this on my end as well and it seems that your observation is correct. The limit/rank filter at the dashboard layer looks at each color/series instead of ranking each bar/group as a whole. This looks like a bug. At the worksheet layer it behaves as expected.

Yes it does behave as expected at the worksheet layer, but then it affects all the visualizations. Is there any work around for this that you can suggest?

Arathi, yes, the workaround is as follows:

Instead of applying a limit and a rank in a dashboard, you rank in the worksheet, and create a new field that shows your aggregation only if the rank under the limit, 0 otherwise.

The limit (how many chart bars to display) will now be a new worksheet parameter, that you would need to create in the worksheet and visualize in the dashboard.

The bart chart that needs this will use this new field (which aggregates only the top # of items, based on the parameter sent from the dashboard), whereas the other bar charts that you don’t want limited this way, will continue using the original aggregation. On the dashboard, you will then simply apply a filter to that one chart, filtering out any 0 values in this new field.

Example: Say my worksheet has sales orders, 1 row per order, with fields:
Sale Amt
Country
Discount Tier (text field with values of either “<=15%” or “15%”.

I have a chart, showing Sales by Country and by Discount Tier, and I want to display top N countries by sales. The user will be able to enter a number on the dashboard, that will control the number of countries to show in the chart.

There are also other charts showing Sales by product etc, and those charts should show all the data, regardless of how many countries are displayed in the first chart.

On the worksheet:

  1. Create a worksheet parameter [Country Rank Parm]
  2. Create a Country level
  3. Add formula to the Country level: [Country Rank By Sales] = Rank([Sale Amt - Sum by Country], “desc”)
  4. Add formula to the country level: [Sale Amt - Sum with Limit]= If([Country Rank by Sales] <= [Country Rank Parm], [Sale Amt - Sum by Country], 0)
  5. Create a new Level 1 - Discount Tier. Create a formula in it: [Sale Amt - Sum by Discount Tier] = Sum([Sale Amt])
  6. Create a chart, using [Sale Amt - Sum by Discount Tier] for Values, Country for Axis and [Discount Tier] for color. Sort this chart by [Sale Amt - Sum by Discount Tier] , descending order

At this point, the chart will show data for the Top N countries, with the rest of the countries showing no sales.

The rest of the steps will be done on the dashboard.

On the dashboard:
7. Add a dashboard control, that will allow the user to type in (or select from the list) the number of countries to show in the chart:

  1. Add another dashboard control - an invisible filter, that will hide countries that has 0 sales. Set it to be placed into the Control Bar (Hidden From Viewer), then click “Apply” to close the dashboard controls window :

  1. Now go to the control bar and the top left and set the Sale Amt Sum … filter to from 1 to max (which will filter out zeros.

Screen Shot 2021-01-04 at 1.10.49 PM

Now, the end result is what we want:

Note that other charts, based on the Sale Amount, that you don’t want to follow the Top N country limit that we set up, should simply use the [Sale Amt - Sum by Country] formula for Values, instead of the [Sale Amt - Sum with Limit].

1 Like

I have tried this method but there is a problem with this. If you see the screenshot you have attached it shows the same value for both “≤15%” and “>15%” (i.e. the Orange and blue part in the graph). These are actually different but are shown having the same magnitude. Also, if I want to sort them that does not work either. The Screen shot attached by me in the first post is after trying the same method.

@arathi , I fixed that, by adding item #5 and modifying item #6 above. Thanks for catching this!