Bug 79140 - Allow "Top 10" Auto Filter to Include Strings
Summary: Allow "Top 10" Auto Filter to Include Strings
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: AutoFilter
  Show dependency treegraph
Reported: 2014-05-23 16:07 UTC by Joel Madero
Modified: 2021-06-16 13:13 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

Top10 example (64.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-26 16:46 UTC, pierre-yves samyn

Note You need to log in before you can comment on or make changes to this bug.
Description Joel Madero 2014-05-23 16:07:57 UTC
Currently top 10 is limited to numbers which can be a limitation compared to other software out there.

The request is to enhance top 10 auto filter to allow filtering out the "top 10" most popular strings in a column.

To test:

1. Put in "a" in A1-A10
2. Data -> filter -> auto filter
3. Select the down arrow on the auto filter in A1 and select "top 10"

Observed: Everything is filtered out

Expected: A stays because it is in the top 10 string cases in the column (it's the only string case ;) )
Comment 1 pierre-yves samyn 2014-05-25 12:39:09 UTC

I confirm, this would be an interesting improvement.
I set status to New.

In the meantime a workaround requires an additional column with eg 

Comment 2 Joel Madero 2014-05-25 17:23:12 UTC
Can you explain that workaround a bit more - I want to add that info to the wiki :)
Comment 3 pierre-yves samyn 2014-05-26 16:45:50 UTC

(In reply to comment #2)
> Can you explain that workaround a bit more - I want to add that info to the
> wiki :)

The "top 10" filter works only on values. You can insert a column in which the CODE function is used to get a numeric code for the first character in a text string.

First, to ignore the difference between uppercase and lowercase, the cell is converted (LOWER function)

Codes corresponding to the aphabet are growing. They are multiplied by -1 so that A is before B.

This workaround is limited because it only takes into account the first letter of the cell. I made ​​this proposal without testing but it seems that however it may give a good result (see attached example)

Go further: in the example calculations in column B are masked by the use of a custom cell style.
That said, the numbers are always displayed when selecting the filter so it may not be necessary

Comment 4 pierre-yves samyn 2014-05-26 16:46:25 UTC
Created attachment 99881 [details]
Top10 example
Comment 5 GerardF 2014-05-26 19:48:39 UTC
I think Joel want to filter the most frequently used strings:
"The request is to enhance top 10 auto filter to allow filtering out the "top 10" *most popular strings* in a column."
not alphabetic order.

You can replace Pierre-Yves formula with this one:
Comment 6 pierre-yves samyn 2014-05-27 07:38:28 UTC

(In reply to comment #5)
> I think Joel want to filter the most frequently used strings

This is not my bad english... 
these are my glasses that need to be improved lol

Thank you Gérard
Comment 7 stragu 2021-06-16 13:13:54 UTC
Still not available in current daily master build:

Version: / LibreOffice Community
Build ID: de7356c2e0cb099fac396808b5a86a0393b48e5f
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2021-06-14_22:38:17
Calc: threaded

I like this idea, but would suggest that names should change to clarify what they do. I think the name "Top 10" can be interpreted in many different ways, hence the confusion here when using it on non-numeric data. I propose:

- "Top 10" becomes "Highest 10": the 10 highest numerical values (and therefore the 10 most recent datetime values too)
- new item: "10 most frequent": regardless of data type, the 10 most frequent values in the column (the enhancement request described here)
- new item: "First 10": regardless of data type, the 10 first values when the column is sorted in alphabetical (unicode) order