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
Created attachment 140975 [details] aggregate function does not work properly for hidden row
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.
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)
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
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.
(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
Also reproduced in Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
(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)
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?
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.
(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
(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 ;-)
I'll take the challenge.
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.
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.
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
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
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