Bug 116706 - AGGREGATE COUNTA with hidden rows
Summary: AGGREGATE COUNTA with hidden rows
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3 all versions
Hardware: All All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: target:6.1.0 target:6.0.4
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2018-03-30 09:15 UTC by Thierry BUR
Modified: 2018-04-16 10:38 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
aggregate function does not work properly for hidden row (11.36 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-03-30 09:16 UTC, Thierry BUR
Details
Numeric calculation using the result of AGGREGATE() (11.89 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-03-30 12:43 UTC, Thierry BUR
Details
Sample file modified to show the different with a single cell range and a range with begin:end (12.31 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-03-30 17:21 UTC, m_a_riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Thierry BUR 2018-03-30 09:15:02 UTC
Description:
When hidding rows in a structured table, AGGREGATE() doesn't behave correctly.

There is a formula that permits to check wether a row is hidden or not by counting the current row cell of a range fully populated:
=--(AGGREGATE(3; 5; [@[Dummy filled]) > 0).

When hidding the row, the count function shoud return 0 and the test return FALSE. One can easily check this by filtering the filled column (rejecting one row, see example file).

A similar formula with SUBTOTAL behaves correctly

Steps to Reproduce:
1. Open attached file
2. Filter/unfilter one row (for exemple the 2nd row vy unchecking XXX in the filter of the A column
3. See the result of the aggregate function that is copied below in B5. 

Actual Results:  
B5 is TRUE

Expected Results:
B5 should be FALSE


Reproducible: Always


User Profile Reset: No



Additional Info:
[Information automatically included from LibreOffice]
Locale: fr
Module: StartModule
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: no


User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36 Edge/15.15063
Comment 1 Thierry BUR 2018-03-30 09:16:43 UTC
Created attachment 140975 [details]
aggregate function does not work properly for hidden row
Comment 2 m_a_riosv 2018-03-30 09:51:22 UTC
FALSE it's is zero showed with a TRUE/FALSE format, there is only a format issue, resetting the format shows the zero.

Result formatted as TRUE/FALSE should came by using the comparision formulas.
Comment 3 Thierry BUR 2018-03-30 12:38:18 UTC
Hi Miguel-Angel,

You might have checked this too quickly, it's not just a "format matter".

To convince you, I slighly modified the file with a SUMPRODUCT computation that uses the "Visible" column as weight (normally 1 for visible rows and 0 for hidden rows).
The SUMPRODUCT computation should result in 3 when both rows are visible, and result in 2 when 2nd row is hidden. But it returns 3 instead of 2.

When all rows are visible, yellow and green cells are the same.

And after I've hidden the 2nd row, the results are no more the same. The green ones (with SUBTOTAL) are the wished good results :

Please doublecheck the herejoined file (V2)
Comment 4 Thierry BUR 2018-03-30 12:43:18 UTC
Created attachment 140977 [details]
Numeric calculation using the result of AGGREGATE()

Look carefully at the differences between:
- C5 and E5
- C9 and E9
They should give the same result (this is the case under Excel) whatever the 2nd row is hidden thanks to the filter or not.

When you uncheck in the 1rst column the value "XXX", ie., you hide the 2nd column, the results in B5 and B9 are wrong.

Please double check
Comment 5 m_a_riosv 2018-03-30 17:21:31 UTC
Created attachment 140985 [details]
Sample file modified to show the different with a single cell range and a range with begin:end

My mistake I was seeing about the format issue.

Seems the function doesnt accept a single cell A2 but work with a range like A2:A2

Please  @Winfried can you take a look.
Comment 6 GerardF 2018-03-31 08:09:08 UTC
(In reply to m.a.riosv from comment #5)

> Seems the function doesnt accept a single cell A2 but work with a range like
> A2:A2
> 
> Please  @Winfried can you take a look.

SUBTOTAL had the same bug in older versions.
This was fixed by Eike :

https://bugs.documentfoundation.org/show_bug.cgi?id=64384
Comment 7 Xisco Faulí 2018-04-03 15:39:56 UTC
Also reproduced in

Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
Comment 8 Winfried Donkers 2018-04-04 05:01:02 UTC
(In reply to m.a.riosv from comment #5)
> Created attachment 140985 [details]
> Sample file modified to show the different with a single cell range and a
> range with begin:end
> 
> My mistake I was seeing about the format issue.
> 
> Seems the function doesnt accept a single cell A2 but work with a range like
> A2:A2
> 
> Please  @Winfried can you take a look.

Will do, but please add me as cc to bug report when you want me to respond ;)
(I came across this bug report via meta bug#108827)
Comment 9 Winfried Donkers 2018-04-04 08:49:10 UTC
I may miss something important here but:
1. AGGREGATE is only introduced with version 4.3.0, so how can this occur with versions 4.1.x and 4.2.x?
2. AGGREGATE has an argument 'option'. In  attachment 140975 [details] this argument has value 5, meaning that _hidden_rows_are_to_be_ignored_. 

One of the main differences between AGGREGATE and SUBTOTAL is this option-argument.

The description mentions steps to reproduce, but in step 2 it says filter/unfilter  one row and in actual and expected results it doesn't say whether these results are for hidden or for non hidden row.
I cannot (yet) reproduce an erroneous result for AGGREGATE.
Could someone explain with step by step instructions how to reproduce an incorrect result?
Comment 10 m_a_riosv 2018-04-04 15:41:15 UTC
Please Winfried forgive for not cc.

In the attachment of my comment #5,
C7=AGGREGATE(3;B7;$Feuil1.$A3) => l, bad. (red)
C11==AGGREGATE(3;B11;$A3:$A3)=> 0, good. (green)
B7=B11=5 (5 => Ignore only hidden rows), row 3 is hidden.
what makes the different it's beetwen $A3 ($Feuil1. doesn't matter) and $A3:$A3, doesn't work with a single cell but so with a range.
Comment 11 Xisco Faulí 2018-04-05 12:26:17 UTC
(In reply to Winfried Donkers from comment #9)
> I may miss something important here but:
> 1. AGGREGATE is only introduced with version 4.3.0, so how can this occur
> with versions 4.1.x and 4.2.x?

I change the earliest affected version to 4.1 because the value was also incorrect using the file attached by  m.a.riosv
Adjusting it and putting it back to NEW
Comment 12 Winfried Donkers 2018-04-05 16:01:22 UTC
(In reply to m.a.riosv from comment #10)
> Please Winfried forgive for not cc.
Absolutely, I wasn't offended in any way, just teasing you ;-)

> In the attachment of my comment #5,
> C7=AGGREGATE(3;B7;$Feuil1.$A3) => l, bad. (red)
> C11==AGGREGATE(3;B11;$A3:$A3)=> 0, good. (green)
> B7=B11=5 (5 => Ignore only hidden rows), row 3 is hidden.
> what makes the different it's beetwen $A3 ($Feuil1. doesn't matter) and
> $A3:$A3, doesn't work with a single cell but so with a range.

Ah, yes, finally I see the problem.
AGGREGATE(3;5;A2:A4) works fine, but AGGREGATE(3;5;A3) doesn't.
If I can find the cause I'll assign this bug to myself and fix it.
After all, chances are that I introduced the bug as well ;-)
Comment 13 Winfried Donkers 2018-04-05 16:10:15 UTC
I'll take the challenge.
Comment 14 Commit Notification 2018-04-09 17:03:37 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0a4c1fb68c3619e61099a7c548f550b0d3fd7a53

tdf#116706 fix for AGGREGATE with hidden rows and single references.

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2018-04-09 18:51:38 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2c51260d91490a6fc512875d8befb38367bf3227&h=libreoffice-6-0

tdf#116706 fix for AGGREGATE with hidden rows and single references.

It will be available in 6.0.4.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Xavier Van Wijmeersch 2018-04-10 15:26:48 UTC
ok in

Version: 6.1.0.0.alpha0+
Build ID: 77e2d29bce92639498324814be56656f85a0f0fd
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 17 m_a_riosv 2018-04-15 14:09:58 UTC
And Ok for me
Version: 6.1.0.0.alpha0+
Build ID: f80029445e2b558f0d0e0a25c2c1bbcbe5254120
CPU threads: 4; OS: Windows 10.0; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-04-13_23:07:11
Locale: es-ES (es_ES); Calc: group
Comment 18 Xavier Van Wijmeersch 2018-04-16 10:38:20 UTC
works also with

Version: 6.0.4.0.0+
Build ID: 158f6e5489d8167299deb53fde433e835019d10a
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group