Bug 77927 - Make Pivot Table much faster: Data fields should be at column, rather than at row
Summary: Make Pivot Table much faster: Data fields should be at column, rather than at...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.0.0.alpha0+ Master
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-04-25 14:06 UTC by Kevin Suo
Modified: 2016-03-06 00:13 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
a ods file which shows how pivot should be like to make it much faster and useful (398.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-04-25 14:06 UTC, Kevin Suo
Details
Pivot layout (35.27 KB, image/jpeg)
2014-05-02 17:23 UTC, GerardF
Details
Sample file modified. (473.10 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-03 10:01 UTC, m.a.riosv
Details
Test ods file showing the improvements in 4.3 version (216.56 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-11 09:14 UTC, Kevin Suo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2014-04-25 14:06:20 UTC
Created attachment 97954 [details]
a ods file which shows how pivot should be like to make it much faster and useful

Just see my attached 10000-row example. Try to add one more data field, you will see that calc takes a long time to draw the data.

Currently when you insert a pivot table, data fields are placed at rows. This makes pivot table freeze and useless in large spreadsheet files which contain a large amount of rows. 
For example, for a pivot table with 10000 rows in the row field, when you add 2 data fields you get 20000 rows, when add 3 data field you get 30000 rows....

The most importantly, "data fields at rows" is not good for data analysis. I guess very few people like this layout. The good layout of pivot table should be like this:
http://excelshortcuts.org/wp-content/uploads/2013/10/Pivot-tables.jpg
Comment 1 Kevin Suo 2014-05-02 01:34:01 UTC
I see from here:
http://cgit.freedesktop.org/libreoffice/core/commit/?id=4f1f8b8e993b98095bf50c9e432fb0400d318b1f

that Tomaž Vajngerl has done some pivot table layout change. however I do not understand the codes issue. maybe my enhancement request is already incorperated in the above commit?

I tried to cc Tomaž Vajngerl but failed.
Comment 2 GerardF 2014-05-02 17:23:11 UTC
Created attachment 98346 [details]
Pivot layout

It is implemented in the future 4.3, see attachment.

When you create a pivot table, you see a field named "Data" in the column field.
This field let you choose if you want data fields in the same column or not.
(You can drag him either in Row or Column).

He is by default in Column witch is what you want, I think.
Comment 3 m.a.riosv 2014-05-03 10:01:16 UTC
Created attachment 98369 [details]
Sample file modified.

If I have understood right, this is possible now, in released versions, dragging the field by their head directly in the sheet from row to column (without enter in edit layout).

Seems that drag in the sheet doesn't work with:
Win7x64Ultimate
Version: 4.3.0.0.alpha1+ Build ID: 0b03f7ed575838f90e6b1ebec3538a3a214f81fb
   TinderBox: Win-x86@39, Branch:master, Time: 2014-04-30_01:30:46

But works dragging "Data" from row fields to column fields in edit layout mode.

In relation with update time, I think master are always a bit slower than released.
Comment 4 Kevin Suo 2014-05-21 15:34:31 UTC
I confirm this is fixed in libreoffice 4.3 alpha 1.
Default data field is in column, and users can drag the "DATA" field to rows if needed.
Thanks!
Comment 5 Kevin Suo 2014-07-11 09:14:58 UTC
Created attachment 102605 [details]
Test ods file showing the improvements in 4.3 version

This is a really great improvement,

I think it should belong to the release notes:
https://wiki.documentfoundation.org/ReleaseNotes/4.3
Comment 6 Joel Madero 2016-02-21 18:56:05 UTC
I realize this won't be undone but as someone who uses a lot of pivot tables and data manipulation - having a completely false and non-existent "data" field was ultra confusing as "data" didn't correspond with a single field in any of my spreadsheets. 

Not a huge fan but I realize it won't be undone
Comment 7 Tomaz Vajngerl 2016-03-05 21:41:14 UTC
(In reply to Joel Madero from comment #6)
> I realize this won't be undone but as someone who uses a lot of pivot tables
> and data manipulation - having a completely false and non-existent "data"
> field was ultra confusing as "data" didn't correspond with a single field in
> any of my spreadsheets. 
> 
> Not a huge fan but I realize it won't be undone

It won't be undone as the "data" field has a purpose - however it doesn't need to be done as it is now - so do you have any suggestions how to improve the dialog?
Comment 8 Joel Madero 2016-03-06 00:13:43 UTC
Well I think at minimum there should be a real field there....then the issue is "what field" in which case it'll be entirely a guess. I think "data" makes absolutely no sense, at least using a real field (an actual column that exists) makes it so users aren't like WTF when they see "data" in the row.

I think the best guess is the first column of the selected data - from my extensive use of pivot tables, this is generally (although not always) one of the fields that is used to query on