Issue 70076 - Data pilot does not group correctly on dates
Summary: Data pilot does not group correctly on dates
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.3
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: oooqa
Depends on:
Reported: 2006-10-03 15:54 UTC by douglasxnichols
Modified: 2017-05-20 11:13 UTC (History)
2 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

Spreadsheet with bad pivot table (10.02 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-02-04 01:10 UTC, douglasxnichols
no flags Details
example for correct data pilot use (11.99 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-02-04 07:43 UTC, Rainer Bielefeld
no flags Details
May be a draft showing the expected behaviour (14.04 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-02-13 18:26 UTC, Rainer Bielefeld
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description douglasxnichols 2006-10-03 15:54:23 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.
Comment 1 Rainer Bielefeld 2007-02-03 11:34:59 UTC
Please attach a sample file!
Please specify your OS and Platform!
Comment 2 douglasxnichols 2007-02-04 01:09:28 UTC
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
Comment 3 douglasxnichols 2007-02-04 01:10:27 UTC
Created attachment 42715 [details]
Spreadsheet with bad pivot table
Comment 4 Rainer Bielefeld 2007-02-04 07:42:40 UTC
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. 

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
Please feel free to open new issue, if you believe that HELP for this might be
I hope these comments will be helpful for you, thank you for your contribution.

Comment 5 Rainer Bielefeld 2007-02-04 07:43:48 UTC
Created attachment 42719 [details]
example for correct data pilot use
Comment 6 Rainer Bielefeld 2007-02-13 06:36:08 UTC
No objection, so CLOSED
Comment 7 douglasxnichols 2007-02-13 17:44:59 UTC
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.
Comment 8 Rainer Bielefeld 2007-02-13 18:04:35 UTC
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
<> and  

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.
Comment 9 Rainer Bielefeld 2007-02-13 18:26:22 UTC
Created attachment 42990 [details]
May be a draft showing the expected behaviour
Comment 10 Rainer Bielefeld 2007-02-13 18:28:29 UTC
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.
Comment 11 douglasxnichols 2007-02-13 19:39:28 UTC
Here is what I would expect:

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


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?
Comment 12 douglasxnichols 2007-02-13 19:43:12 UTC
Sorry, your example is more what I expect to see.

Comment 13 frank 2007-02-14 15:18:07 UTC

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.

Comment 14 Rainer Bielefeld 2007-02-14 15:49:14 UTC
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 ....
Comment 15 Marcus 2017-05-20 11:13:18 UTC
Reset assigne to the default "".