Bug 93171 - Subtotal function with function indexes 101-111 don't update when rows are hidden (hard recalculation is needed)
Summary: Subtotal function with function indexes 101-111 don't update when rows are hi...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.0.3 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.4.0 target:5.3.4 target:5.4....
Keywords: implementationError
: 106985 (view as bug list)
Depends on:
Blocks: Function-Subtotal
  Show dependency treegraph
 
Reported: 2015-08-06 04:08 UTC by Rajendra Parmar
Modified: 2017-09-15 19:51 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
Subtotal function example Worksheet (10.09 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-08-06 04:54 UTC, Rajendra Parmar
Details
Sample files in LO and Excel showing working of Subtotal() function (8.64 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-07-15 22:15 UTC, Rajendra Parmar
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Rajendra Parmar 2015-08-06 04:08:12 UTC
In data manipulation one frequently needs to check and cross check totals by hiding or unhiding  some of the data fields. It is therefore of utmost importance that hiding / unhiding should have direct and automatic effect on the resultant figure (total).  The Excel has this function which is very much helpful. 
Because of this limitation in CALC, millions of libreoffice users have to turn to Excel. Inclusion of this functionality will enhance the utility value of the Libreoffice and will attract millions of people to use it.

I had previously reported this as bug and requested to improve the functionality but it is not considered any seriously. See it is not cosmetic enhancement but it is improvement of a critical nature which will go a long way in establishing LO as the market leader.
Comment 1 Rajendra Parmar 2015-08-06 04:54:04 UTC
Created attachment 117697 [details]
Subtotal function  example Worksheet
Comment 2 raal 2015-08-06 08:53:04 UTC
(In reply to Rajendra Parmar from comment #0)
> 
> I had previously reported this as bug and requested to improve the
> functionality but it is not considered any seriously.

Which bug number? Thank you
Comment 3 GerardF 2015-08-06 12:30:15 UTC
(In reply to raal from comment #2)
> (In reply to Rajendra Parmar from comment #0)
> > 
> > I had previously reported this as bug and requested to improve the
> > functionality but it is not considered any seriously.
> 
> Which bug number? Thank you

Found it :
https://bugs.documentfoundation.org/show_bug.cgi?id=87043
and this bug has been tagged as duplicate of https://bugs.documentfoundation.org/show_bug.cgi?id=60477

Still open. I think it is necessary to solve this bug in order to comply with ODF specifications. (Even if I use SUBTOTAL only with filters)
Comment 4 raal 2015-08-06 12:50:12 UTC
So this bug is again duplicate of bug 60477? As I read the descriptions, I doesn't see a difference between this bug a bug 87043.

Closing this as duplicate of bug 60477. 

@Rajendra, if this bug is different than 60477, set again to unconfirmed and please  explain why it's different. If it's duplicate, please do not create new bugs. Thanks

*** This bug has been marked as a duplicate of bug 60477 ***
Comment 5 raal 2015-08-07 10:44:05 UTC Comment hidden (obsolete)
Comment 6 raal 2015-08-07 10:46:43 UTC
Played again, bug 60477 is resolved, =SUBTOTAL(109;B3:B18)  works as expected.
But this bug is about recalculation after hiding row.

Steps to reproduce>
 - open attached file. B19 and B36 = 1017  (=SUBTOTAL(109;B3:B18) )
 - select row 14 - hide row

Actual results>
 B19 = 1017

HARD RECALCULATION: CTRL+SHIFT+F9
  B19 = 615


Expected results:
  B19 = 615

LO 4.4.5, win7
Comment 7 Michel Rudelle 2015-12-11 10:45:52 UTC
(In reply to raal from comment #6)
> Played again, bug 60477 is resolved, =SUBTOTAL(109;B3:B18)  works as
> expected.
> But this bug is about recalculation after hiding row.

Hi,
I confirm that very annoying need to force a recalculation
Version 5.0.3.2 and 5.1.0.0.beta1-buildfix1 / Vista 32b
Best regards
Comment 8 QA Administrators 2017-01-03 19:47:32 UTC Comment hidden (obsolete)
Comment 9 Aron Budea 2017-04-08 23:20:23 UTC
*** Bug 106985 has been marked as a duplicate of this bug. ***
Comment 10 Aron Budea 2017-04-08 23:34:32 UTC
Adjusting earliest version, since function indexes 101-111 started working in 4.4.0.3.
Comment 11 Aron Budea 2017-04-27 12:44:14 UTC
Arul Michael, are you planning to work on this issue, or you just wanted to add yourself to the CC list?
Comment 12 Arul Michael 2017-04-27 13:39:51 UTC
(In reply to Aron Budea from comment #11)
> Arul Michael, are you planning to work on this issue, or you just wanted to
> add yourself to the CC list?

I am planning to work on this.
Thanks
Arul
Comment 13 Commit Notification 2017-05-11 23:04:44 UTC
Arul Michael committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=970b431f1a7b6b96c4c9536657ce4fe9d8f5b585

tdf#93171 Subtotal function with function indexes don't update

It will be available in 5.4.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 14 Commit Notification 2017-05-12 00:32:35 UTC
Arul Michael committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=318b6b8ea87cba41995436bbc44818a068b35b6d&h=libreoffice-5-3

tdf#93171 Subtotal function with function indexes don't update

It will be available in 5.3.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 15 Commit Notification 2017-05-18 11:14:44 UTC
Arul Michael committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=47cbf098ee6019a2090b2e933439fd4aa399ed20

New HintId and unit test for hidden rows and SUBTOTAL, tdf#93171 follow-up

It will be available in 5.4.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 16 Commit Notification 2017-05-18 14:16:47 UTC
Arul Michael committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=70ca2d0bc42a0360055b9c54e6e5edd3ef9317b8&h=libreoffice-5-4

New HintId and unit test for hidden rows and SUBTOTAL, tdf#93171 follow-up

It will be available in 5.4.0.1.

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 17 Xisco Faulí 2017-07-14 13:28:09 UTC
Polite ping: is this bug fixed? if so, please close it as RESOLVED FIXED
Comment 18 Rajendra Parmar 2017-07-14 15:49:05 UTC
I have tested the function and found that it omits the hidden row for the range selected for the SUBTOTAL() but it does not effect subsequent referred cells. Referred cells result does not change.  This may be addressed.
Comment 19 Xavier Van Wijmeersch 2017-07-15 19:06:46 UTC
I tested and it works; hiding row14; its calculate automatic the subtotal

Version: 6.0.0.0.alpha0+
Build ID: 8c82e4877181f55f2fe186b341d504e5782a4f9c
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group

Version: 5.3.4.2
Build ID: SlackBuild for 5.3.4 by Eric Hameleers
CPU Threads: 8; OS Version: Linux 4.9; UI Render: default; VCL: kde4; Layout Engine: new; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 20 Rajendra Parmar 2017-07-15 22:15:13 UTC
Created attachment 134655 [details]
Sample files in LO and Excel showing working of Subtotal() function

I have also attached Similar subtotal example prepared in LO Calc but it does not appear above. If not linked, the receiver is requested to prepare the same file in LO to check the operation of function in LO.
Comment 21 Xavier Van Wijmeersch 2017-07-20 14:30:22 UTC
Referred cells result does not change.  This may be addressed.
I tested it again and yes your wright

Version: 6.0.0.0.alpha0+
Build ID: 1ec04aa8d8f37c82c78d6b98e030a34769d3123e
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-07-18_22:38:27
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 22 Eike Rathke 2017-08-23 09:22:03 UTC
This bug is not assigned (anymore), changing to NEW.
Comment 23 Eike Rathke 2017-08-24 16:16:42 UTC
Investigating.
Comment 24 Commit Notification 2017-08-24 17:36:03 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#93171 follow-up, notify dependents of subtotal formula cells

It will be available in 6.0.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 25 Eike Rathke 2017-08-24 17:51:27 UTC
Pending review https://gerrit.libreoffice.org/41530 for 5-4
5-3 is not affected.
Comment 26 Commit Notification 2017-09-15 19:51:13 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=de4a0f769501b0b79b05e007ae340af344017dd0&h=libreoffice-5-4

Resolves: tdf#93171 follow-up, notify dependents of subtotal formula cells

It will be available in 5.4.2.

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.