Bug 160515

Summary:	FILEOPEN XLSX Formula referencing Pivot table cells leaves incorrect result after pivot table resizing
Product:	LibreOffice	Reporter:	Gabor Kelemen (allotropia) <kelemeng>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	UNCONFIRMED ---
Severity:	normal	CC:	aron.budea, miguelangelrv
Priority:	medium
Version:	4.3.0.4 release
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	112416
Attachments:	Example file from Excel 2016 The Query sheet in Excel and Calc before changing the filtering The Query sheet in Excel and Calc after changing the filtering

Description Gabor Kelemen (allotropia) 2024-04-04 07:37:56 UTC

Created attachment 193480 [details]
Example file from Excel 2016

Attached Excel made file contains a pivot table and on another sheet some simple formula referencing the range where pivot results are expected.

When the pivot table is resized, the last entry on the other sheet remains, leaving an incorrect result.

1. Open attached file
2. Go to the Query sheet, observe it's filled with non-0 values until row 35
3. Go to the Costs sheet and change the filter in B1, uncheck the D5 entry
4. Go back to the Query sheet
-> Row 35 is still filled, but rows before it are not anymore

Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: c9d4f99b2bb54ac7735b3e0d0cadbafbd0279518
CPU threads: 15; OS: Windows 10.0 Build 19045; UI render: default; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: default

Seems to have started in 4.3 with:

https://cgit.freedesktop.org/libreoffice/core/commit/?id=aa5ad7b8096cd15a55c467b1a23d03849aeb870d

author	Kohei Yoshida <kohei.yoshida@collabora.com>	2014-03-10 17:03:52 -0400
committer	Kohei Yoshida <kohei.yoshida@collabora.com>	2014-03-10 17:28:54 -0400
commit aa5ad7b8096cd15a55c467b1a23d03849aeb870d (patch)

fdo#74747: Make use of cached string formula results.

But before this B35 was just empty, and values in column A were also present in the filtered out rows as well (this was solved in 4.4), so not really good either.

Comment 1 Gabor Kelemen (allotropia) 2024-04-04 07:41:06 UTC

Created attachment 193481 [details]
The Query sheet in Excel and Calc before changing the filtering

Comment 2 Gabor Kelemen (allotropia) 2024-04-04 07:45:02 UTC

Created attachment 193482 [details]
The Query sheet in Excel and Calc after changing the filtering

Comment 3 m_a_riosv 2024-04-04 11:27:51 UTC

Seems a hard-recalc solves the issue. 
Maybe the issue is the Autocalculte is not triggered for $Query.B35 because their formula is out of the PT range.

Also a hard-recalc is pending at opening the file before apply the filter.
At opening $Query.B35 has not the right value.

With the option in
Menu/Tools/Options/LibreOffice Calc/Formula/Recalculate on file load — For Excel 2007 and newer
active, at list on this case solve the issue.

Version: 24.2.2.2 (X86_64) / LibreOffice Community
Build ID: d56cc158d8a96260b836f100ef4b4ef25d6f1a01
CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win
Locale: es-ES (es_ES); UI: en-US
Calc: CL threaded