Problem with showing top-n results in an horizontal bar chart with series

danidiaz

I have an horizontal bar chart. Let's say the Y axis is factories, while the X axis is number of incidents in each factory in some time period.

I want to show only the 10 factories with the most incidents, sorted by number of incidents.

When I'm not using any series, the simplest way of doing that is to sort on COUNT of ID_INCIDENT, and then limit rows to 10. Very easy. Here is a dummy example:

The problem comes when I add a series. Incidents are classified by urgency: LOW, MEDIUM, HIGH. (I have a URGENCY_INDEX beast mode that assigns a numeric value to each one.)

When I add the series (only as a series, without sorting by it) the COUNT of ID_INCIDENT sorting ceases to work correctly. The rows are no longer ordered by total incidents:

Is there a way to have an horizontal stacked bar chart that is sorted by totals, but has a series, and where the series are sorted within each bar by URGENCY_INDEX?

I attach a zipped .csv with the sample data to reproduce the problem.

fac.zip

danidiaz

I made some advances, but a complete solution still feels elusive.

I find the documentation for sorting confusing. At least three methods are cited:

• Using chart properties like "sort on totals" / "sort each category".
• Using the sorting field.
• Using the "Quick Sort".

I thought that "Quick Sort" was an epheral setting that wasn't saved in the cart itself (you can change the quick sort and exit the Card Analyzer without being prompted with a save dialog). But turns out changing it inside Analyzer does stick.

Trying to use the sorting field exclusively—sorting on the COUNT of ID_INCIDENT and also on the URGENCY_INDEX beast mode—didn't work. Maybe it's explained by this bit of documentation:

"When doing a multiple sort, you should only sort on values present in the card (x-axis, y-axis, and series)."

URGENCY_INDEX is a beast mode based on the series, not the series itself.

The hackish partial solution I've found is to put URGENCY_INDEX alone on the sorting field, and then use "Quick Sort" to sort on totals.

Thas does what I want (sorting rows by totals, and by URGENCY_INDEX within each bar) except that in my real card I have many bars, and setting the "Limit Rows" to some concrete value jumbles the order of everything again :(

So, there's no way I've found of having these three things at the same time:

• Sort bars by total.
• Within each bar, sort the URGENCY series by the URGENCY_INDEX beast mode.
• Have a Limit on the number of bars.

danidiaz

Some more progress. If instead of limiting rows with

I use the "maximum items" from Chart Properties | General

then the ordering doesn't get humbled.

(Why are there two similar ways of limiting rows? Why does one work and the other doesn't?)

The only remaining problem is that now I have an unwanted "Other" row at the bottom of the chart.

jaeW_at_Onyx

@danidiaz what you're trying to do, AFAIK cannot be done without a Window Function and RANK or ROW_Number

to get your "other" category, you'll have to caluclate the rank inside of a Dataset View and then build a CASE statement on top of the RANK column.

Here's a bit of a long video but somewhat comprehensive.

Also, ask your CSM to enable "window functions in Analyzer"

https://www.youtube.com/watch?v=39VHAQEYHAc&t=920s

Saketh

@danidiaz - you have an option to hide the "other" category on your card under the general chart properties section!