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)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
Keywords: filter:xlsx
Depends on:
Blocks: Pivot-Table-XLSX
  Show dependency treegraph
Reported: 2019-04-13 09:58 UTC by Mike Kaganski
Modified: 2023-10-14 10:10 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

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

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. 
Version: (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
Comment 2 QA Administrators 2021-10-13 03:40:37 UTC Comment hidden (obsolete)
Comment 3 QA Administrators 2023-10-14 03:16:58 UTC Comment hidden (obsolete)
Comment 4 m_a_riosv 2023-10-14 10:10:34 UTC
Version: (X86_64) / LibreOffice Community
Build ID: 9eb419b0b0f019f5fbc48ff1a11977e8b041edee
CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: es-ES (es_ES); UI: en-US
Calc: CL threaded Jumbo