Bug 124716 - Pivot table's page field's cell data type does not match type of value in data source
Summary: Pivot table's page field's cell data type does not match type of value in dat...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: Pivot-Table-XLSX
  Show dependency treegraph
 
Reported: 2019-04-13 09:58 UTC by Mike Kaganski
Modified: 2019-04-13 23:02 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
A simple pivot table with filter on mixed-type content (14.84 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-04-13 09:58 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2019-04-13 09:58:00 UTC
Created attachment 150727 [details]
A simple pivot table with filter on mixed-type content

In the attached spreadsheet, week # is used to filter data in the pivot table (page field). Excel sets cell B1 data type based on which data type was in the data source; so when selecting week 1, =FORMULATEXT(B1) returns TRUE, and for week 2 (which is made text in the data source) it returns FALSE. Using B1 in LOOKUP also succeeds for week 1 when searching over numbers; but fails (correctly) for week 2.
In LibreOffice, the type of B1 is unconditionally text. LOOKUP fails for any week used in the filter.

See https://ask.libreoffice.org/en/question/190242 for real-life problem because of this.
Comment 1 m.a.riosv 2019-04-13 23:02:28 UTC
It happens also with dates, may be some relation, as example dates in GETPIVOTDATE() must be introduced as text in the criteria. 
Repro
Version: 6.2.3.2 (x64)
Build ID: aecc05fe267cc68dde00352a451aa867b3b546ac
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US
Calc: CL