Created attachment 136259 [details] spreadsheet demonstrating the stale data after opening I have a spreadsheet with formulas comparing data from pivot tables. After opening the document, some of the data is stale, and I have to manually refresh the pivot tables to get correct results. Unfortunately saving the file does not make the refresh persistent, and I have to repeat every time. Attached is an example spreadsheet, demonstrating the issue. I've tried to make it smaller, but it seems to make the problem go away. The spreadsheet contains data from a benchmark (timing of database queries), and there are three types of sheets: * raw data (query, info about database settings, timing of executions) * pivot tables (summarizes raw data) * comparison of the pivot tables (first sheet) So essentially the flow is "raw -> pivot -> comparison". To demonstrate the issue, look at cells D3, K3 and Y3 on the "comparison" sheet. The cells have values 65, 64 and 1.04 (104%). However Y3 = K3/D3, and clearly 64/65 is not 1.04, in fact it's less than 1.0. Now, go to "master default pivot" sheet, with one of the pivot tables, and refresh it manually (right-click + refresh). If you go back to the "comparison" sheet, you'll see that D3 and K3 did not change, but Y3 is now 0.99. The initially shown data may be coming from some older version of the spreadsheet, with data from previous runs (I've only replaced the raw data, expecting the pivot tables and comparison to update itself). I've noticed this on LO 5.2, but updating to 5.4 did not fix the issue.
I think has nothing to do with pivot tables, D3 and K3 doesn't change their values updating pivot table, edit Y3 add a space to get it modified without change and intro. it is recalculated, do a hard recalc [Ctrl+Shift+F9] There is an option just to avoid this cases to recalculate on load. Menu/Tools/Options/LibreOffice Calc/Formula - Recalculation on file load.
(In reply to m.a.riosv from comment #1) > I think has nothing to do with pivot tables, D3 and K3 doesn't change their > values updating pivot table, edit Y3 add a space to get it modified without > change and intro. it is recalculated, do a hard recalc [Ctrl+Shift+F9] > > There is an option just to avoid this cases to recalculate on load. > Menu/Tools/Options/LibreOffice Calc/Formula - Recalculation on file load. You're right it may not be related to pivot tables - but what is the cause, then? I mean, this is the first spreadsheet where I noticed this, so there has to be something special about it. I wonder what is it? The main problem for me was that the correct values were not saved, so after re-opening the file the stale data was back. But Ctrl-Shift-F9 seems to fix that, which is nice. I don't think setting the "Recalculation on file load" really solves the issue. It may solve it for me, but I need to share such spreadsheets with other people, and who knows if they have this enabled.
After recalculation if I save the file, after reloading it looks fine. There is no way to solve this if wrong value was saved with the file.
(In reply to m.a.riosv from comment #3) > After recalculation if I save the file, after reloading it looks fine. > There is no way to solve this if wrong value was saved with the file. Well, the question is what counts as recalculation. For me: 1) refresh pivot table + save : does not fix the issue (the values are recalculated, but after reopening the file I get the stale data again) 2) refresh pivot table + edit cell + save : fixes the issue (adding a space to a random cell is enough) 3) Ctrl+Shift+F9 + save : fixes the issue Initially I was only doing (1), which was quite frustrating, as every time I had to do it again after reopening the file. The fact that (2) fixes it makes me a bit puzzled. I don't really like (3) as it expects an action from everyone who gets the file. If they fail to do that, they'll get incorrect data.
1) works fine for me, after reload right value for Y. So I can't see a bug here. Letting as unconfirmed,some else take a look.
(In reply to Tomas from comment #4) > 3) Ctrl+Shift+F9 + save : fixes the issue > > I don't really like (3) as it expects an action from everyone who gets the > file. If they fail to do that, they'll get incorrect data. But there is no action needed, if you send them the corrected file. So let's close.