Bug 138478 - CALC: Dynamic conditional colour formatting in autofiltered arrays
Summary: CALC: Dynamic conditional colour formatting in autofiltered arrays
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.3.1 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsUXEval
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2020-11-25 08:22 UTC by Colin
Modified: 2021-01-15 12:01 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Simple example sheet with an effective data volume (110.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-01-11 17:39 UTC, Colin
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Colin 2020-11-25 08:22:48 UTC
The function [SUBTOTAL] will provide the defined analysis - sum, min, max, avg, etc., for the displayed cells from within an autofiltered array.
If the component cells are also subject to Format > Conditional > Colour Scale then prior to the auto filtering, the colour rendition is graduated across the entire range of the cell values.
Following auto filtering, the colour rendition does not change so the likelyhood is that all the cells will be varying hues of the same colour if multiple instances of a similar event are filtered.
I feel it would be appropriate for the user to be in a position to define that the colour scale should re-define itself to represent the low, middle & high values of the resulting display, thereby more clearly highlighting the cell value extremes.
Perhaps a radio button either with the colour scale definition or the [SUBTOTAL] function which is defaulted to static evaluation. That is to say - the user MUST choose a dynamic colour rendition.
I'm not sure if eXcel has a similar facility but obviously, importing and exporting are a major consideration if Dynamic Colour Scaling proves to be unique to LO.
Note: [SUBTOTAL] obviously provides values for min, max, etc., but there's still a significant effort required to "scan" a list of potentially similar values to identify those most closely matching the defined SUBTOTAL
Comment 1 Heiko Tietze 2021-01-11 05:48:02 UTC
Could you please attach a document with a minimum example?
Comment 2 Colin 2021-01-11 09:01:29 UTC
(In reply to Heiko Tietze from comment #1)
> Could you please attach a document with a minimum example?


I can confirm that I am currently using Release version 7.0.4.2 as opposed to version 7.0.3.1 which was active at the time of the request.

I just constructed a small sheet which should have demonstrated what I was suggesting and it now performs as I would have expected from the suggested enhancement.

I originally believed my request and the existing behaviour were both valid scenarios but must now assume somebody had already flagged it as a bug - which has now been remedied.

Should I set it to RESOLVED - NOT A BUG or is that your domain?
Comment 3 Heiko Tietze 2021-01-11 12:56:46 UTC
Whether magically or silently fixed, happy to see it working for you. Feel free to reopen in case.
Comment 4 Colin 2021-01-11 17:39:51 UTC
Created attachment 168828 [details]
Simple example sheet with an effective data volume
Comment 5 Colin 2021-01-11 17:45:52 UTC
(In reply to Heiko Tietze from comment #3)
> Whether magically or silently fixed, happy to see it working for you. Feel
> free to reopen in case.

My entire text was deleted because I submitted the sheet at the same time as I posted the comment.

Sheet one shows a filtered data set and the colour rendition covering the whole spectrum. so one column is almost entirely red (un-filtering will demonstrate the complete spectrum)
Sheet two shows a paste special of the filtered event where the colour format has been created to cover the data ranges it encounters and produces the colour spectrum suggested by my request.
Comment 6 Heiko Tietze 2021-01-15 11:41:02 UTC
Probably a misconception. Let's take a simple example

Group	Value
1	1
1	1
1	2
2	2
2	3
2	3

If you apply the color scale to value 1 becomes green, 2 yellow, and 3 red. If you copy the group 1 and apply the same color scale you don't have the full range and 2 becomes red.

Without having read the initial request: doing the calculation based on the current filter makes absolutely no sense. A filter is not a subset.
Comment 7 Colin 2021-01-15 11:54:40 UTC
(In reply to Heiko Tietze from comment #6)
> Probably a misconception. Let's take a simple example
> 
> Group	Value
> 1	1
> 1	1
> 1	2
> 2	2
> 2	3
> 2	3
> 
> If you apply the color scale to value 1 becomes green, 2 yellow, and 3 red.
> If you copy the group 1 and apply the same color scale you don't have the
> full range and 2 becomes red.
> 
> Without having read the initial request: doing the calculation based on the
> current filter makes absolutely no sense. A filter is not a subset.

Precisely the substance of the request. The implication is that the boundaries are set by the original group and it would be nice if there were some mechanism by which the boundaries could be changed by the selection of a subset to reflect the new highs & lows extant in the subset.
I think what you're saying is - Not possible/desirable/logical via the auto-filter/SUBTOTAL() mechanism and that a user would have to copy and paste special the data and then re-define the conditional colour spectrum for each and every iteration of paste special.
Comment 8 Heiko Tietze 2021-01-15 12:01:09 UTC
You define the range in the color scale dialog. No idea how to work around this but the request is a WF. Adding Eike for an idea - and you can always go to ask.libreoffice.org