Bug Hunting Session
Bug 115334 - variant Icon set in Conditional formatting cant be setup
Summary: variant Icon set in Conditional formatting cant be setup
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3.5.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Conditional-Formatting-Editing
  Show dependency treegraph
 
Reported: 2018-01-31 15:16 UTC by Eric
Modified: 2019-09-07 20:05 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Screen Shot of 'Conditions' dialog showing issue (21.94 KB, image/png)
2018-01-31 15:18 UTC, Eric
Details
spreadsheet showing issue (8.37 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-04-08 05:56 UTC, Eric
Details
different screenshots of 3 arrows formatting (223.99 KB, application/vnd.oasis.opendocument.text)
2018-04-08 07:15 UTC, Xavier Van Wijmeersch
Details
sheet showing icon color (13.52 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-07 15:04 UTC, Eric
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Eric 2018-01-31 15:16:52 UTC
Description:
The icon set in conditional formatting cant be set up.
There are 3 conditions: <0 =0 >0 but the 3 arrows are mapped to conflicting tests e.g >=0 and <=0 and the third icon cant be mapped at all
I will attach a screen shot.
I verified this in 3 different versions with the .config/libreoffice directory and with it deleted (in case there was something there causing the issue)
5.3.5.2
5.4.1.2
6.0.0.0.alpha1 (I know, its what i had downloaded awhile ago)




Steps to Reproduce:
1. open a new spreadsheet
2. select format->Conditional Formatting->Icon Set


Actual Results:  
unable to setup the 3 condtions

Expected Results:
Should be able to setup the 3 conditions and they should be mutually exclusive


Reproducible: Always


User Profile Reset: Yes



Additional Info:
OpenSuse Leap 42.3
KDE 5.32.0   Plasma 5.8.7  Qt version 5.6.2


User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0
Comment 1 Eric 2018-01-31 15:18:10 UTC
Created attachment 139468 [details]
Screen Shot of 'Conditions' dialog showing issue
Comment 2 Buovjaga 2018-02-24 12:58:00 UTC
So you mean you cannot type into the fields? No such problem for me.

Arch Linux 64-bit
Version: 6.0.1.1
Build ID: 6.0.1-1
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Comment 3 Eric 2018-02-24 15:17:28 UTC
One field there is not a box to type into but also look at the logic of the arrows, its not mutually exclusive as it should be (see the image i attached in my original post) They are using >= and <= instead of >, =, <
Comment 4 Markus Mohrhard 2018-02-25 16:12:06 UTC
(In reply to Eric from comment #3)
> One field there is not a box to type into but also look at the logic of the
> arrows, its not mutually exclusive as it should be (see the image i attached
> in my original post) They are using >= and <= instead of >, =, <

They are exclusive in the sense that first the >= condition for green is evaluated, then the >= for yellow and if none of the two matches the last condition (red) is taken. The equivalent pseudo code would be:

if (val >= green_limit)
    set(green)
else if (val >= yellow_limit(
    set(yellow)
else
    set(red)


Your proposal is unable to handle more than 3 conditions and does not allow to set a range for the middle condition.
Comment 5 Eric 2018-04-04 14:48:27 UTC
I set the following:
=sign(k2-j98) in cell k98
This gives me 1 or -1 or 0 (not sure if i will ever get a zero but)
In conditional formatting Icon set, i set the yellow arrow to 0 and the green to 1

The value in the cell is now -1 and a green arrow, it should be a red arrow

What i am trying to do is to track a changing value compared to a static value
I want to  see an appropriate icon if the dynamic value rises above or falls below the static value, if its equal i should see a yellow
Comment 6 Markus Mohrhard 2018-04-07 15:14:05 UTC
(In reply to Eric from comment #5)
> I set the following:
> =sign(k2-j98) in cell k98
> This gives me 1 or -1 or 0 (not sure if i will ever get a zero but)
> In conditional formatting Icon set, i set the yellow arrow to 0 and the
> green to 1
> 
> The value in the cell is now -1 and a green arrow, it should be a red arrow
> 
> What i am trying to do is to track a changing value compared to a static
> value
> I want to  see an appropriate icon if the dynamic value rises above or falls
> below the static value, if its equal i should see a yellow

Please attach a test document with detailed instructions how to reproduce the problem.
Comment 7 Eric 2018-04-08 05:56:05 UTC
Created attachment 141202 [details]
spreadsheet showing issue

Try playing with Value1 and Value2 in cells B1 and B2. Watch Cell B5
formula in B5 is =SIGN(B2-B1)
Comment 8 Eric 2018-04-08 05:58:06 UTC
Please note: 
I will not be able to respond to this bug for the next 30 days as i will be out of the country.
Comment 9 Xavier Van Wijmeersch 2018-04-08 07:15:06 UTC
Created attachment 141203 [details]
different screenshots of 3 arrows formatting

I think the problem is more a wrong interpretation of =sign() by the reporter.

SIGN
Returns the sign of a number. Returns 1 if the number is positive, -1 if negative and 0 if zero.
Syntax
SIGN(Number)
Number is the number whose sign is to be determined.

So he need to change percent into value and it will work.
For me its not a bug, more a miss understanding off the function sign
Comment 10 Xisco Faulí 2018-06-04 12:05:12 UTC
(In reply to Xavier Van Wijmeersch from comment #9)
> Created attachment 141203 [details]
> different screenshots of 3 arrows formatting
> 
> I think the problem is more a wrong interpretation of =sign() by the
> reporter.
> 
> SIGN
> Returns the sign of a number. Returns 1 if the number is positive, -1 if
> negative and 0 if zero.
> Syntax
> SIGN(Number)
> Number is the number whose sign is to be determined.
> 
> So he need to change percent into value and it will work.
> For me its not a bug, more a miss understanding off the function sign

Dear Eric,
Could you please try what Xavier is suggesting?

I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' once you have retested it
Comment 11 Eric 2018-06-05 05:31:50 UTC
Ok, if i select value it works. but why is percent even an option then?
All in all i have to say this whole option is very confusing. From not having any value to put i for red arrows to using the same logical comparison for green and yellow( >= ) to percent apparently not working. Can this not be re-worked to make it clear what is expected and what will happen?
Comment 12 Xisco Faulí 2018-06-06 10:51:32 UTC
(In reply to Eric from comment #11)
> Ok, if i select value it works. but why is percent even an option then?
> All in all i have to say this whole option is very confusing. From not
> having any value to put i for red arrows to using the same logical
> comparison for green and yellow( >= ) to percent apparently not working. Can
> this not be re-worked to make it clear what is expected and what will happen?

@markus, Do you have an answer for the questions above?
Comment 13 Xisco Faulí 2019-05-16 12:22:13 UTC
Dear Eric,
A new major release of LibreOffice is available since this bug was reported.
Could you please try to reproduce it with the latest version of LibreOffice
from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.
Comment 14 Eric 2019-08-11 00:53:53 UTC
I am now using version 

Version: 6.2.5.2
Build ID: 20(Build:2)
CPU threads: 12; OS: Linux 5.2; UI render: default; VCL: kde5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

And it works as you described. I still wish this were clearer, possibly a tool tip that explains whats expected, although please see bug https://bugs.documentfoundation.org/show_bug.cgi?id=126494
as that one makes this bug even more confusing.
Comment 15 mattia.b89 2019-09-07 09:08:48 UTC
I think this bug is related to this one: https://bugs.documentfoundation.org/show_bug.cgi?id=127416
Comment 16 Eric 2019-09-07 15:04:13 UTC
Created attachment 154007 [details]
sheet showing icon color

In version 6.2.6.2 the down arrow icon color is wrong, it should be red not green but it seems this is only a problem if Karasa Jarga font is selected. I think I had a bug filed on that but cant find it.
Comment 17 Xavier Van Wijmeersch 2019-09-07 19:52:01 UTC
no repro with; I think the problem is solved

Version: 6.3.0.2
Build ID: 728469fa359ba8c83d812146293a0b0aa53945ba
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded