Created attachment 149991 [details]
File for quick reproduction of the bug

Sheet 1 - source data, copy to any new sheet to experiment with
Sheet 2 - results of failed reset filter operation.

reproducible with:

Version: 6.1.5.2 (x64)
Build ID: 90f8dcf33c87b3705e78202e3df5142b201bd805
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: de-DE (de_DE); Calc: 

and AOO 4.1.5

steps to reproduce:
- open attached spreadsheet
- select column A
- Menu Data/More Filters/Standard Filter...
  Column A = Not Empty
  [x] No Duplications
- Select e.g.: A49:A173
- Menu Data/More Filters/Reset Filter
-> A1:A48 and A174:A245 have hidden rows

having empty cells in the filtered range produces 'reset filter' greyed out on: 

Version: 7.1.0.0.alpha0+ (x64)
Build ID: <buildversion>
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win
Locale: de-DE (de_DE); UI: en-US
Calc: CL

Reproduced in:

Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: 94d552f94b427f884c004dba5d4619ecf729d605
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-18_13:30:27
Calc: threaded

Resetting the filter when one single cell in the range is selected does reset the filter for the whole column. So I see this as inconsistent.

Reproducible in:

Version: 7.5.4.2 (X86_64) / LibreOffice Community
Build ID: 36ccfdc35048b057fd9854c757a8b67ec53977b6
CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win
Locale: pl-PL (pl_PL); UI: pl-PL
Calc: CL threaded

Steps to reproduce:
1. Open attached file and in Sheet 1 apply standard filter to collumn A, Non empty and no duplicates works best.
2. Select a range inside filtered rows. for example 26:106
3. Reset filter

Results:
Rows 1:25 Are still filtered out (only 4 visible rows)
Rows 26:106 have all rows visible
Rows 107:245 are still filtered out (only 10 rows are visible)
Reset filter is grayed out

Expected result:
All rows should be visible after filter reset

Consequences of the bug for the user:
If user selects a range that includes last visible row while sheet is filtered (say to copy filtered column to a separate sheet), then after resetting with the selection still on, the user may not notice, that last x number of rows is still hidden and may perform various operations on incomplete data set, possibly even ending up with bad/inadequate results afterwards. 

Try selecting 1:231 in given file and look, that you have to scroll all the way down to row 231 and look at the row counter to notice, that some rows are hidden.

IMHO...

(In reply to Julian Ragan from comment #6)

> 2. Select a range inside filtered rows. for example 26:106
> 3. Reset filter

> Expected result:
> All rows should be visible after filter reset

While I have the same behavior, I disagree with the expected result. I think that once you select a specific range, then resetting the filter should affect the selected range only.

Regarding the "Reset Filter" action being grayed out after using it once, the way to resolve it is to get into the "Standard Filter" dialog and "Clear" the data in/of the dialog. I indeed agree that there might be some possible enhancement in this part of the report.

Apply filter and select rows 1:231 (and no further) as if for copying filtered data to a separate location and reset the filter with selection still on. You will notice, that rows 231 - 246 remain hidden.

Another quirk - if user filters column A and selects 1:246 then reset filter is greyed out.

IMO Standard filter works on an entire columns when applied, so when reset it should reset it completely.

But if a partial reset within selected range should be an option, then it should be shown in the context menu of the selected range of rows, also it should check, if selected rows on one of the ends of selection are not next to empty rows to avoid leaving last set of rows hidden after resetting the filter within selected range.

Also in menu Data -> More filters there should be an additional entry "Remove filter" to remove it entirely.

Current behavior is hard to notice on larger data sets for users that don't expect it.

(In reply to Julian Ragan from comment #8)
> Apply filter and select rows 1:231 (and no further) as if for copying
> filtered data to a separate location and reset the filter with selection
> still on. You will notice, that rows 231 - 246 remain hidden.

Not exactly, and this is the same as in comment 7:
1. Apply the filter as described in STR.
2. Select range 1:231 (both extremes are already shown, not hidden).
3. Reset filter. 1:231 are all shown, 232:245 remain hidden, as they were not selected and they were all hidden before step 2. Row 246 was not hidden before step 2, and remains not hidden after step 3.

So, same behavior as in comment 7.


> Another quirk - if user filters column A and selects 1:246 then reset filter
> is greyed out.

Again, as in comment 7: Menu Data > More Filters > Reset Filter is available only once, even if we then select a new different range that is still affected by some filter; this could/should be improved.


> IMO Standard filter works on an entire columns when applied, so when reset
> it should reset it completely.

Not necessarily, because multiple standard Filters could be used/applied to multiple areas/columns. These are not necessarily simultaneously used, but they indeed affect the resulting hidden/displayed rows cumulatively.

As I said, there is potential improvement, but resetting every-and-all filters while a specific range is already selected while resetting would be taking a feature away.

As said in comment 7, to completely reset the filter you can use the "Clear" button within the Standard Filter dialog, whether the Reset Filter menu entry was already used or not.


> But if a partial reset within selected range should be an option,

It is already an available feature, whether unintentionally or not. It should remain a feature. I do agree that the menu entry should be improved, to be available more than once when adequate.


> But if a partial reset within selected range should be an option, then it
> should be shown in the context menu of the selected range of rows, also it
> should check, if selected rows on one of the ends of selection are not next
> to empty rows to avoid leaving last set of rows hidden after resetting the
> filter within selected range.
> 
> Also in menu Data -> More filters there should be an additional entry
> "Remove filter" to remove it entirely.
> 
> Current behavior is hard to notice on larger data sets for users that don't
> expect it.

Whatever changes are made, the fact that multiple non-simultaneous filters can be applied should always be considered. The effects are cumulative, but the filters are not all applied at the same time in one-and-only dialog.

(In reply to ady from comment #9)
> (In reply to Julian Ragan from comment #8)
> > Apply filter and select rows 1:231 (and no further) as if for copying
> > filtered data to a separate location and reset the filter with selection
> > still on. You will notice, that rows 231 - 246 remain hidden.
> 
> Not exactly, and this is the same as in comment 7:
> 1. Apply the filter as described in STR.
> 2. Select range 1:231 (both extremes are already shown, not hidden).
> 3. Reset filter. 1:231 are all shown, 232:245 remain hidden, as they were
> not selected and they were all hidden before step 2. Row 246 was not hidden
> before step 2, and remains not hidden after step 3.
> 
> So, same behavior as in comment 7.

This behavior at the very least should be considered buggy, there is no way to select rows 232:246 and reset filter, also if you reset filter once, you cannot clear filter later to show rows that were hidden by filter and not shown by filter reset. It seems that reset filter command orphans filtered out rows as no longer filtered and sets them hidden. But this may be an effect of expecting Reset Filter to work in such a case like clear filter in filter dialog.

> > Another quirk - if user filters column A and selects 1:246 then reset filter
> > is greyed out.
> 
> Again, as in comment 7: Menu Data > More Filters > Reset Filter is available
> only once, even if we then select a new different range that is still
> affected by some filter; this could/should be improved.

No, I mean user applies filter and selects more rows, than were affected by filter, then resetting filter is not available. But that may be a result of me confusing filter reset with clearing filter in filter dialog.

> > IMO Standard filter works on an entire columns when applied, so when reset
> > it should reset it completely.

This was a feature misunderstanding on my part. Sorry.

> Not necessarily, because multiple standard Filters could be used/applied to
> multiple areas/columns. These are not necessarily simultaneously used, but
> they indeed affect the resulting hidden/displayed rows cumulatively.
> 
> As I said, there is potential improvement, but resetting every-and-all
> filters while a specific range is already selected while resetting would be
> taking a feature away.
> 
> As said in comment 7, to completely reset the filter you can use the "Clear"
> button within the Standard Filter dialog, whether the Reset Filter menu
> entry was already used or not.
> 
> 
> > But if a partial reset within selected range should be an option,
> 
> It is already an available feature, whether unintentionally or not. It
> should remain a feature. I do agree that the menu entry should be improved,
> to be available more than once when adequate.
> 
> 
> > But if a partial reset within selected range should be an option, then it
> > should be shown in the context menu of the selected range of rows, also it
> > should check, if selected rows on one of the ends of selection are not next
> > to empty rows to avoid leaving last set of rows hidden after resetting the
> > filter within selected range.
> > 
> > Also in menu Data -> More filters there should be an additional entry
> > "Remove filter" to remove it entirely.
> > 
> > Current behavior is hard to notice on larger data sets for users that don't
> > expect it.
> 
> Whatever changes are made, the fact that multiple non-simultaneous filters
> can be applied should always be considered. The effects are cumulative, but
> the filters are not all applied at the same time in one-and-only dialog.

So it seems, but it leads to unexpected results for users without large experience in using filtering feature. Also reset filter command is easily confused with clear filter.

Perhaps a more in depth look at current feature set is necessary.

(In reply to Julian Ragan from comment #10)

> This behavior at the very least should be considered buggy, there is no way
> to select rows 232:246 and reset filter, also if you reset filter once, you
> cannot clear filter later to show rows that were hidden by filter and not
> shown by filter reset. It seems that reset filter command orphans filtered
> out rows as no longer filtered and sets them hidden. But this may be an
> effect of expecting Reset Filter to work in such a case like clear filter in
> filter dialog.

JIC, to avoid possible misunderstandings (for future readers), let me point out that those are inaccurate statements, but I don't want to get OT.

The important point for this bug / enhancement request is that
 Menu Data > More Filters > Reset Filter 
needs to be available more than once, so filters on/of additional ranges/areas could (selectively) be "Cleared" accordingly.

(In reply to ady from comment #11)
> (In reply to Julian Ragan from comment #10)
> 
> > This behavior at the very least should be considered buggy, there is no way
> > to select rows 232:246 and reset filter, also if you reset filter once, you
> > cannot clear filter later to show rows that were hidden by filter and not
> > shown by filter reset. It seems that reset filter command orphans filtered
> > out rows as no longer filtered and sets them hidden. But this may be an
> > effect of expecting Reset Filter to work in such a case like clear filter in
> > filter dialog.
> 
> JIC, to avoid possible misunderstandings (for future readers), let me point
> out that those are inaccurate statements, but I don't want to get OT.
> 
> The important point for this bug / enhancement request is that
>  Menu Data > More Filters > Reset Filter 
> needs to be available more than once, so filters on/of additional
> ranges/areas could (selectively) be "Cleared" accordingly.

The problem I have reported caused me some pain due to incomplete data analysis due to missing some hidden rows at the end of a large dataset, but after this discussion it mostly seems to be borne out of my own misconceptions about how filtering works in Calc.

I am not sure if this is something that can be remedied by making an enhancement to Reset filter, this may be a problem with command naming and command placement, that leads inexperienced users to making wrong assumptions about what which command does. And after all this time, to make changes to this would be a problem for people who have already learned how to use it properly. 

After all, user should read the docs, not make assumptions.

(In reply to Julian Ragan from comment #12)
> (In reply to ady from comment #11)
> > (In reply to Julian Ragan from comment #10)

> After all, user should read the docs, not make assumptions.

I have also reviewed the docs for filter and reset filter, and this behavior seems undocumented as far as I can find in the following page: https://help.libreoffice.org/latest/lo/text/scalc/guide/database_filter.html

Other mentions in help files for reset filter also mention only removal of filter, nothing about it being dependent on user selection.

So even if I read the docs at the time (which I can't remember now) I think I would have gotten the wrong idea on how to remove filter from a range either way.

(In reply to Julian Ragan from comment #13)

> So even if I read the docs at the time (which I can't remember now) I think
> I would have gotten the wrong idea on how to remove filter from a range
> either way.

(In reply to ady from comment #9)
> > But if a partial reset within selected range should be an option,
> 
> It is already an available feature, whether unintentionally or not. It
> should remain a feature. I do agree that the menu entry should be improved,
> to be available more than once when adequate.

The "Clear" button clears it all. The Reset Filter menu entry is currently working on (partial) selected ranges, or, if no range is selected, it works on all the data.

Let's wait for input from UX, instead of cluttering the report with noise.

To add another example: 1,2,3,3 in A1:A4 and 1,2,3,3 in A6:A9. Click A1 and apply the filter to the first group of numbers, and then the same for the second. Selecting the whole row does not enable Reset, neither selecting A1 to the end (shift+ctrl+down) but A6.

We could simply solve the problem by extending the selection to the whole block as it happens for applying a filter - you cannot filter A1:A3 separately from A4:A6 but need some empty cell between.

(In reply to Heiko Tietze from comment #15)
> Selecting the whole row does not enable Reset, neither selecting A1
> to the end (shift+ctrl+down) but A6.

You have not filtered any set of values at all, so there is no Reset available.

> We could simply solve the problem by extending the selection to the whole
> block as it happens for applying a filter - you cannot filter A1:A3
> separately from A4:A6 but need some empty cell between.

FWIW, I don't understand what you mean. The ranges you are mentioning here are not clear to me.

At any rate, selecting menu Data > Autofilter once should add an autofilter. Selecting the same menu entry a second time (whether having the list already filtered or not) should clear the relevant filter (according to active cell) and should also remove the autofilter down arrow. This is also useful when the list does not include a header and the first item is filtered out (thus, the autofilter down-arrow is hidden in such case).

Created attachment 188234 [details]
Screencast

(In reply to ady from comment #16)
> FWIW, I don't understand what you mean.

The filter cannot be applied to a selection, it always expands to the full range. We should do the same for Reset (and don't disable).

(In reply to Heiko Tietze from comment #17)
> The filter cannot be applied to a selection, it always expands to the full
> range.

That's incorrect. Both the Standard Filter and the AutoFilter can be applied to a _selection_ that does not include every-and-all adjacent cells. Just select a range and try; for instance, leave the first cell of the range out of the selection before activating the (auto)filter.

> We should do the same for Reset (and don't disable).

That suggestion is a contradiction, and it goes against what I already explained in prior comments. Currently, the Reset acts on the _selection_; and when there is no specific selection, it acts on the filter that is relevant to the range that includes the active cell.

My comments are still consistent with your screencast of comment 17; it just happens that you are not selecting any specific range/cell, and that the Reset menu entry acts only on 1 of the areas – I don’t know what’s the algorithm for it.

If possible, "Reset" should be allowed to act multiple times (unless there is no filter left active to reset on any area), but should not expand automatically to the whole range when a specific range is selected; it should act on the selected range only.

The "Clear" button acts on the whole affected area. There might be possible enhancements there too.

(In reply to ady from comment #18)
> (In reply to Heiko Tietze from comment #17)
> > The filter cannot be applied to a selection, it always expands to the full
> > range.
> 
> That's incorrect. Both the Standard Filter and the AutoFilter can be applied
> to a _selection_ that does not include every-and-all adjacent cells.

You are right, the selection is not extended into leading cells. But if you select something like A1:A4 the trailing A5 will be added.

Why not extending the selection in both directions? Is there a good use case to sort/filter data only partially?

(In reply to Heiko Tietze from comment #19)
> Why not extending the selection in both directions? Is there a good use case
> to sort/filter data only partially?

This report is about how to use the Reset menu entry and potential improvements to it. There seem to be some inconsistencies (and potential improvements) in its usage. Other changes are not desired unless specific useful (not just hypothetical) cases are presented. The current usage of sort/filter are not part of this report.

Actual results:
* "Reset" can be used on selected area - this is useful.
* "Reset" acts only once, or none.
* "Clear" button in Standard Filter resets everything.

Expected results:
* Multiple uses of "Reset" should be possible, depending on the selected range.

Alternative:
* If the current usage/consequences are not what they were supposed to be, then improve the way in which the couple, "Reset" menu entry and "Clear" button (in Standard Filter), are really supposed to work in order to either reset partial ranges or the complete filter, according to users' desires. It is preferable to make all this work in a user-friendly manner, as changing these procedures can be confusing to users that already use them.


After resolving the adequate steps and usage, better documentation is needed.

(In reply to ady from comment #20)
> The current usage of sort/filter are not part of this report.

As a consequence of how inconsistent Reset works it is a matter of discussion.

Actual results:
* "Reset" cannot be used in case of incomplete selections
* Complete selection is not always possible 

Expected results:
* Reset removes the filter regardless any selection

I think my proposal is clear: automatically select the range up and down in both cases when applying a filter and when resetting it.

(In reply to Heiko Tietze from comment #21)

> Actual results:
> * "Reset" cannot be used in case of incomplete selections
> * Complete selection is not always possible 

I've already posted regarding such conditions; based on my experience, I have to disagree with those statements.

> 
> Expected results:
> * Reset removes the filter regardless any selection

Again, that goes against prior comments (of mine).

> 
> I think my proposal is clear: automatically select the range up and down in
> both cases when applying a filter and when resetting it.

That means that "Reset" (menu entry) and the "Clear" button (in Standard Filter) would provide the same result, and the current _partial_ reset/clear would no longer be available.

I have nothing more to add regarding this matter.