Apache OpenOffice (AOO) Bugzilla – Issue 70076
Data pilot does not group correctly on dates
Last modified: 2017-05-20 11:13:18 UTC
Enter two columns of data: 1 - some dates over a year for example and change the date format so it only shows the months- see below 2 - some amounts for example actual date: 01/01/2006 $69.76 01/02/2006 $93.28 01/03/2006 $244.74 01/04/2006 $70.90 01/08/2006 $37.98 01/10/2006 $57.34 01/15/2006 $20.12 01/19/2006 $15.79 01/20/2006 $75.96 Formatted date: January $69.76 January $93.28 January $244.74 January $70.90 January $37.98 January $57.34 January $20.12 January $15.79 January $75.96 Now start data pilot, put the date in the rows and sum on the amount( second column). The results are incorrectly grouped. Resulting in a ow for each row in the original data.
@douglasxnichols Please attach a sample file! Please specify your OS and Platform!
So I am now using the Mac OS X kernel and so I am using: 2.1 the latest OpenOffice- weird you cannot paste from the About OpenOffice item. Mac 10.4.8, X11 1.1.3 - XFree86 4.4.0 See attached spread sheet
Created attachment 42715 [details] Spreadsheet with bad pivot table
I see the effect in 'sample-bad-formatting.ods' and can reproduce the bad data pilot sort problem with "2.0.2 German version WIN XP: [680m5(Build9011)]". It's no OOo problem, but a user error. @douglasxnichols You will also see that the first value is missing in you original data pilot (it became a column heading) table. Please see "sample-bad-formatting_correct" for correct data pilot use: 1. Insert new row '1' 2. Insert column headings "actual date:" to 'A1' and "value" to 'B1' 3. Mark (highlight) 'A1:B10' 4. Menu 'Data - Data Pilot - Access - Current Selection" <oj> 5. In new dialogue drag and drop button "actual date:" to 'row fields' and "value" to 'data fields' 6. Select output area, if you want 7. Start data pilot function with <ok> Yo will see that everything works fine, you can see the result in "sample-bad-formatting_correct". Please feel free to open new issue, if you believe that HELP for this might be misleading. I hope these comments will be helpful for you, thank you for your contribution.
Created attachment 42719 [details] example for correct data pilot use
No objection, so CLOSED
No your example is wrong! The point is you get several rows of january instead of on row with january and a total. I am not talking about row headers I am talking about correctly aggregating the data on the format- Month. The point of the only using january is to point out how obsurd it would be to want a table with a bunch of rows with January instead of one row with january. You could see if you wanted to do a whole year??? You have to go through obnoxious lengths to get the aggregation correctly. You should be able to format the columns with month, quarter, etc and get a aggregation table based on the format not the actual data.
I'm sorry, but I do not understand what you want to reach. It seems that you want to collect and sum values for each month, but I am not sure. All your polemics can not replace a problem description in accordence to our guidelines on <http://qa.openoffice.org/issue_handling/pre_submission.html> and <http://www.openoffice.org/bugs/bug_writing_guidelines.html>. If you think that there is a bug in OOo (means: does not work as designed), please quote a help text, specification or similar that describes the correct behaviour and then demonstrate where OOo does not act as written in that text. If you think that we need an enhancement to improve usefulness of OOo, please contribute an user scenario concerning the requirement and a draft fo the solution.
Created attachment 42990 [details] May be a draft showing the expected behaviour
@douglasxnichols Does 'expected-good-formatting.ods' show the behaviour you want to reach? For this example I replaced the calendar dates by strings with month name to demonstrate the goal as I understood your explications.
Here is what I would expect: data: actual date: Date Amount 01/01/2006 $69.76 01/02/2006 $93.28 01/03/2006 $244.74 01/04/2006 $70.90 01/08/2006 $37.98 01/10/2006 $57.34 01/15/2006 $20.12 01/19/2006 $15.79 01/20/2006 $75.96 Formatted date: Date Amount January $69.76 January $93.28 January $244.74 January $70.90 January $37.98 January $57.34 January $20.12 January $15.79 January $75.96 pivot Table: This is what I get now: Sum of Amount Date Total January 69.76 January 93.28 January 244.74 January 70.90 January 37.98 January 57.34 January 20.12 January 15.79 January 75.96 Total 685.87 ....snip... This is what I think you should get: Date Total January 685.87 Total 685.87 I would suspect since OO knows that the format of the sum column it should be able to know the format of the row and column headers?
Sorry, your example is more what I expect to see. cheers dn
Hi, this is not a defect but an enhancement request. A grouping on the month can easily be achieved by formatting the date column as e.g. YYYY-MM-DD, creating the datapilot, placing the cellcursor into the resulting datapilot date column and pressing F12, Now select Month as grouping option and it will do exactly what is needed. Frank
I do not believe that we can find a simple solution for this, because basic philosophy of data interpretation would be affected. For example filters (try standard filter for 'A1:A9' in "sample-bad-formatting.ods") also do not consider that "January" view. We need a complete specification for such "handle data as visible in cell" proceeding for all affected functions like filters, data pilot, calculation in cells, search and replace, conditional formatting, may be starbasic ....
Reset assigne to the default "issues@openoffice.apache.org".