Bug 132518 - PIVOTTABLE: Filter by year in Drop-down filter window doesn't work
Summary: PIVOTTABLE: Filter by year in Drop-down filter window doesn't work
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.2.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Pivot-Table
  Show dependency treegraph
 
Reported: 2020-04-29 12:27 UTC by Zayed
Modified: 2024-01-31 13:12 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
test data file (15.59 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-04-29 12:27 UTC, Zayed
Details
Correct behavior (78.49 KB, image/png)
2020-04-29 12:28 UTC, Zayed
Details
Wrong Behavior (66.53 KB, image/png)
2020-04-29 12:29 UTC, Zayed
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Zayed 2020-04-29 12:27:55 UTC
Created attachment 160071 [details]
test data file

In the Pivot table, the filter by year in Drop-down filter window doesn't work. It doesn't not group items by year same as standard filter. Please see the screenshots.
Comment 1 Zayed 2020-04-29 12:28:41 UTC
Created attachment 160072 [details]
Correct behavior
Comment 2 Zayed 2020-04-29 12:29:31 UTC
Created attachment 160073 [details]
Wrong Behavior
Comment 3 m_a_riosv 2020-04-29 13:57:31 UTC
Confirmed.
It happens even group for years.

So:
To the sample, add a group for years -> filter for years doesn't work.
After that delete 'Date' field -> filter for years fine.
Add again the 'Date' field -> filter for years fine.

Version: 6.4.3.2 (x64)
Build ID: 747b5d0ebf89f41c860ec2a39efd7cb15b54f2d8
CPU threads: 4; OS: Windows 10.0 Build 19608; UI render: GL; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US Calc: CL
Version: 7.0.0.0.alpha0+ (x64)
Build ID: 7459b9ecb54a298f02d19089620149718f8d8d48
CPU threads: 4; OS: Windows 10.0 Build 19608; UI render: Skia/Raster; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US Calc: CL
Comment 4 Zayed 2021-02-18 06:13:06 UTC
I find a workaround for this issue as follows:
1- edit the pivot table by right click on it and choose Proprieties.
2- Drag the Date field from Filter section to Row Fields. Then click on OK.
3- left click on any cells of Date, then from menu bar select "Data ---> Group and Outline ---> Group".
4- On the "Group by" section select "Years" only.  Then click on "OK" button. 
5- repeat steps 1 to 2 but now return Date field to the Filter Section. Then OK button. 

Now you can filter by year in Drop-down filter.
Comment 5 Andreas Heinisch 2021-11-07 11:49:56 UTC
Unfortunately, there are some flaws in the pivot table especially with date and time. Some testing code:

bool ScSheetDPData::IsDateDimension(sal_Int32 nDim)
{
    CreateCacheTable();
    const ScDPCache::ScDPItemDataVec& rItems = aCacheTable.getCache().GetDimMemberValues(nDim);
    for (const ScDPItemData& rItem : rItems)
    {
        if (rItem.GetType() == ScDPItemData::Type::Date)
        {
            return false;
        }
    }
    return true;
}

However, in initFromCell in dpcache.cxx there is no check for a date/time cell, therefore the above code will not work. In addition, it conflicts with isDataDimension (Data vs Date) and causes a crash. There is even some dead code in dptabsrc.cxx [1] because isDateDimension checks only if the dimension is the first one [2].

[1] https://opengrok.libreoffice.org/xref/core/sc/source/core/data/dptabsrc.cxx?r=f528fff9#1990
[2] https://opengrok.libreoffice.org/xref/core/sc/source/core/data/dpshttab.cxx?r=f477b54e#103
Comment 6 Zayed 2022-01-11 05:16:28 UTC
(In reply to Zayed from comment #4)
> I find a workaround for this issue as follows:
> 1- edit the pivot table by right click on it and choose Proprieties.
> 2- Drag the Date field from Filter section to Row Fields. Then click on OK.
> 3- left click on any cells of Date, then from menu bar select "Data --->
> Group and Outline ---> Group".
> 4- On the "Group by" section select "Years" only.  Then click on "OK"
> button. 
> 5- repeat steps 1 to 2 but now return Date field to the Filter Section. Then
> OK button. 
> 
> Now you can filter by year in Drop-down filter.

one more step, make sure the cells are Date not text.
Comment 7 QA Administrators 2024-01-12 03:13:10 UTC Comment hidden (obsolete)
Comment 8 Zayed 2024-01-31 13:12:48 UTC
Still open in version: 7.6.4.1