Bug 61313 - CONDITIONAL FORMATTING: Icon set should have a color reverse switch
Summary: CONDITIONAL FORMATTING: Icon set should have a color reverse switch
Status: ASSIGNED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: Other All
: medium enhancement
Assignee: Ahmed Hamed
URL:
Whiteboard: reviewed:2023
Keywords: difficultyInteresting, easyHack, skillCpp, skillUI
Depends on:
Blocks: Conditional-Formatting-Editing
  Show dependency treegraph
 
Reported: 2013-02-22 21:42 UTC by Horst
Modified: 2024-03-26 10:15 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Smiley dialog example.png (102.42 KB, image/png)
2018-02-25 07:53 UTC, Gerry
Details
Excel 2010 Screenshot: Reverse Icon Order button (42.59 KB, image/png)
2018-03-03 21:35 UTC, Gerry
Details
Excel 2016 Screenshot: Reverse Icon Order button (.png file) (45.04 KB, image/png)
2018-03-17 11:31 UTC, Gerry
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Horst 2013-02-22 21:42:56 UTC
The new Icon Set should have a switch for color reversal (like Excel). Small values are not always bad (red) and big values are not always good (green).

An alternative would be also an "Icon Set 2 invers colors".
Comment 1 Markus Mohrhard 2013-03-25 08:45:06 UTC
Additionally there should be a way to change each icon but I have no plans to implement that in the near future.
Comment 2 Gerry 2013-11-26 10:11:49 UTC
I second this enhancement request. However, maybe it can be solved easier:

Instead of switching the order of icon sets, why not just switching the signs. It should be possible to select:

"<=" instead of ">=" 

Then, the problem is solved. I assume this is easier to implement?!?
Comment 3 Markus Mohrhard 2017-04-08 18:56:24 UTC
This has been implemented for a long time. The options are just not exposed in the UI.
Comment 4 Markus Mohrhard 2017-04-08 22:48:46 UTC
The remaining part that needs implementation for this feature is to introduce advanced configuration solutions for the icon set entry in the conditional format dialog.

The code for the icon set entry in the dialog is in sc/source/ui/condformatdlg

The code for the icon set model part, including the reverse flag, the configuration that allows any image for an icon can be found in sc/inc/colorscale.hxx and sc/source/core/data/colorscale.cxx
Comment 5 abstatic 2018-02-24 23:28:48 UTC
Hello,

I'd like to work on this, however I don't completely understand this. Can anyone please provide code pointers, and a more clear description of the problem ?
Comment 6 Gerry 2018-02-25 07:49:07 UTC
@abstatic: That's great to hear that you want to work on this bug. When I use icon sets in conditional formatting, I frequently need to use workarounds, because Calc is lacking the feature to reverse the color or icons.

I illustrate the issue with an example using the icon set "3 smileys":
The issue is that both, the order of the smileys :-) :-| :-( AND the order of the sign >= are hard coded. In other words, at the moment, Calc always assigns a :-( to high values. This does not always make sense. 

For example, if you imagine an exam with 100 points. You fail if you have < 50 points, you pass if you have >= 50 points and you get an excellent grade if you have => 75 points. 

What Calc can do (this is wrong!):

Grades (100 points in exam is very good, 0 points is very bad)
100 :-(
95 :-(
30 :-)
22 :-)
55 :-|

What Calc should be able to do (this is correct!):

Grades (100 points in exam is very good, 0 points is very bad)
100 :-)
95 :-)
30 :-(
22 :-(
55 :-|

What we need is that the order can be inversed. IMHO, it should be possible for the user to switch the sign >= to <= if the scale is inverse. Currently, in the dialog, the sign >= is hard coded and cannot be changed by the user. 

Please see the attached screnshot "Smiley dialog example" to illustrate that I cannot change the order. Instead of an >= I need an <= for my scale of exam points.
Comment 7 Gerry 2018-02-25 07:53:33 UTC
Created attachment 140125 [details]
Smiley dialog example.png

For some scales, it is important that the user can inverse the order of the smileys/icons/colours, because high values are not always bad or good. In this example with points in an exam, I cannot correctly assign smileys, because higher values mean a better grade. What we need is an inverse order switch.
Comment 8 Gerry 2018-02-25 07:57:22 UTC
Additional info: Markus Mohrhard had implemented the conditional formatting feature, so he probably is the person who can help you with the code pointers.

He already gave a few hints in comment 4
Comment 9 Ekansh Jha 2018-03-01 14:27:32 UTC
Hi @Markus Could you please elaborate what do you mean by "introduce advanced configuration solutions".
Comment 10 Ekansh Jha 2018-03-03 20:55:24 UTC
Hi @Gerry! I am trying to work on this, and I have took some inspiration from Latest MSexcel. In that conditionalformat dialog one can select from "<","<="etc. 
I have made a list and removing that hard coded label. I would like to ask whether this is accepted or not. 

About the title of this bug:Iconset should have a color reverse switch, In MS Excel 
I see the above feature but I can't see the color reversal switch instead of it one
can find dropdown in which he can select different types of ICon. Should this be implemented or color reverse switch should be ok? Please let me know if I am not clear.
Comment 11 Gerry 2018-03-03 21:34:48 UTC
Hi @Ekansh Jha : Thank you very much for working on this. I think the original bug description was not very precise. I think @Horst actually meant "Icon set should have a reverse order switch". I think Horst had these icon sets in mind, where the icons only differ by color (e.g. the icon set that looks like a traffic light). In other icon sets (e.g. the yellow smilies), it is actually a reverse order switch, which is needed.

On your question how to set the UI, I can give you my opinion and ideas, but I guess that @Markus Mohrhard has a clearer idea how the solution could best look like. So, please ask him, too. I am just a user :-)

I also added @Heiko Tietze to this bug, because he deals a lot with the user experience and design of LibreOffice.

I could imagine following solution:

For ITEM SETS:
I could imagine to have a selection field where currently ">=" is. The selection field could have the two options ">=" and "<". This in fact reverses the order. The selection field could be there for the first icon and then automatically applies for the other icons (to not make the order inconsistent).

[For comparison: Microsoft Excel has a switch called "Reverse Icon Order". Hence, it does not reverse the signs, but just the order of icons. This would also work. Please see attached screenshot]
Comment 12 Gerry 2018-03-03 21:35:55 UTC
Created attachment 140316 [details]
Excel 2010 Screenshot: Reverse Icon Order button
Comment 13 Ekansh Jha 2018-03-03 21:40:33 UTC
(In reply to Gerry from comment #11)
> Hi @Ekansh Jha : Thank you very much for working on this. I think the
> original bug description was not very precise. I think @Horst actually meant
> "Icon set should have a reverse order switch". I think Horst had these icon
> sets in mind, where the icons only differ by color (e.g. the icon set that
> looks like a traffic light). In other icon sets (e.g. the yellow smilies),
> it is actually a reverse order switch, which is needed.
> 
> On your question how to set the UI, I can give you my opinion and ideas, but
> I guess that @Markus Mohrhard has a clearer idea how the solution could best
> look like. So, please ask him, too. I am just a user :-)
> 
> I also added @Heiko Tietze to this bug, because he deals a lot with the user
> experience and design of LibreOffice.
> 
> I could imagine following solution:
> 
> For ITEM SETS:
> I could imagine to have a selection field where currently ">=" is. The
> selection field could have the two options ">=" and "<". This in fact
> reverses the order. The selection field could be there for the first icon
> and then automatically applies for the other icons (to not make the order
> inconsistent).
> 
> [For comparison: Microsoft Excel has a switch called "Reverse Icon Order".
> Hence, it does not reverse the signs, but just the order of icons. This
> would also work. Please see attached screenshot]

Hi @Gerry! actually I have MS16 I think they have removed that color switch icon
(afaik). Looking forward to hear suggestions from @heiko and @markus.
Comment 14 Gerry 2018-03-17 11:31:03 UTC
Created attachment 140670 [details]
Excel 2016 Screenshot: Reverse Icon Order button (.png file)

> Hi @Gerry! actually I have MS16 I think they have removed that color switch
> icon
> (afaik). Looking forward to hear suggestions from @heiko and @markus.

Finally, I got hand at MS Excel 2016. They have not removed the reverse switch. Please see the attachment.

BTW, you see this dialog after (1) having applied a conditional formatting in Excel, then afterwards (2) select edit conditions. Then you see this dialog.
Comment 15 Gerry 2018-03-17 11:38:42 UTC
(In reply to Ekansh Jha from comment #13)
> Looking forward to hear suggestions from @heiko and @markus.

I have seen that Markus was not copied to this bug to answer the open questions how the UI of the fix should look like. So, I felt free to add @Markus Mohrhard to this bug. Maybe, also @Heiko has not seen your question or was busy.


@Markus and @Heiko: The main question here is how the reverse switch for icon sets / color scales in conditional formatting should look like. There is the solution by MS Excel (see the two screenshots) or a solution like mentioned in comment 2 and comment 11.
Comment 16 Heiko Tietze 2018-03-19 11:06:57 UTC
I'm always in favor of simplicity and against awkward controls to manipulate the presented options. So the easiest solution is to add "3 inverse colored smileys" to the list. Should be an easyhack.

But the actual problem here are the hard-coded symbols, as Markus pointed out in comment 1. So what we need is a dropdown with a set of symbols including the smileys, and you select whatever sequence you want. In other words, [Cell value is] [greater than] 10 [Insert] [:-)] (with Insert additional to "Apply Style".

Even better the user can add own symbols from the emojis (ideally colored) or unicode dialog.
Comment 17 Gerry 2018-03-22 21:56:25 UTC
Thanks Heiko for commenting on this bug.

(In reply to Heiko Tietze from comment #16)
> I'm always in favor of simplicity and against awkward controls to manipulate
> the presented options. So the easiest solution is to add "3 inverse colored
> smileys" to the list. Should be an easyhack.

Well, we have 22 icon sets, which means that we would duplicate the icon sets to 44. Does this really make sense?

I also wouldn't say that the idea in comment 11 is an "awkward control". I suggest to have the >= as a button. If you click on it, it changes to < (and automatically changes the signs below to make it consistent):
 _____        _____   
|     |      |     |  
|  >= |  --> |  <  |
|_____|      |_____|

That's straight forward, isn't it?
Comment 18 Hossein 2023-08-24 13:44:47 UTC
Re-evaluating the EasyHack in 2023

This enhancement is still relevant, as the issue is still present in the UI.
One should go to "Format > Conditional > Icon Set..." to see the icon set used for conditional formatting.
Comment 19 Ahmed Hamed 2024-03-26 08:13:46 UTC
I am working on this hack, but I am not sure what is the desired solution. Should I make a switch button to reverse order of icons, or just add a "3 inverse color smilies" to the list ? Or is it something else ?
Comment 20 Heiko Tietze 2024-03-26 10:15:33 UTC
(In reply to Ahmed Hamed from comment #19)
> I am not sure what is the desired solution.
Comment 2 and comment 4 should make it clear: replace the static ">=" label with as dropdown that allows to pick >,>=,<,<= and perhaps <>. 

I think it's not convenient anyway, but no idea what control we have over this (eg. change the icons, the number of items, the order...).