pivot_106846.ods (101.9 KB) (the pivot has a filter, and I made a mistake setting the filter values)
Thanks. I see. I guess the filter is wrong and must be DATE >= AND DATE <=.
Yes, that was my mistake. And you see that the filter works. The source table contains only numbers under a row of column labels.
The numbers were imported from a database in random sort order. You can filter and sort the source data any way you want without affecting the pivot table.
The pivot table aggregates the sums of durations (data field) for each day (row field) in ascending sort order of days. The filter button at cell A1 filters the incoming source data. Any filter buttons within a pivot table filter the resulting row or column fields of a pivot table.
I understand.
But I’m trying hard to get the formatting from my original table to that Pivottable. Just copy/paste for format only doesn’t work like that. Just redoing the conditional formatting in order to have alternating row colors is always lost when re-sorting or refreshing the pivot-table.
pivot_240702-1435.ods (121.5 KB)
Please try to get the format from the original table to the pivot table.
When applying any formatting it’s lost after update. I also tried changing the “Pivot Table …” styles in Manage Styles (F11), but I can’t remove the grid or add some alternating row coloring by Conditional Rules with formula ISEVEN(ROW()).
What is broken on my Date column?
Yes, the entire table is rebuilt from scratch. There is nothing to do about it, except using this formatting for printing/pdf export.
That’s a blocker issue against using Pivot tables. We definitely need to format the filtered result in the pivot-tables.
At least I need to get rid of the grid lines. This does not seem to be part of the Pivot table styles?
I managed to apply a background color to the content by changing style “Pivot Table Category”. But I can’t get some background color set for the column headers.
In the Pivot table filter, how can I use fields for the start and enddate?
By the way: The pivot table in your document is an exact copy of the source table, because the combination of date, start time and end time is unique. The sum of durations always refers to one value in a unique record.
You could simply filter the source table, but that would distort your even/odd conditional formatting because spreadsheets just hide filtered rows instead of actually filter them out.
I tried your new version pivot_240702-1655.ods. Formatting is lost after a refresh, but kept when pressing the Refresh-Button with Macro. Is that the idea?
I know, I actually don’t use all the filtering and mapping of Pivot-Tables. I actually only need a filtering per date range. I could do that via an Advanced filter as well, and also reading filter criteria from table. But that means that I have “per year” tabs that need to reference all from the Data tab. And because the Data tab is always increasing, I don’t know how much to reference to each tab? Ok, I just tried:
I made a copy of the Data tab, replaced all values by references to the Data tab. Formatting is easy there. Then I created the Filter criteria in $‘2023’.$F$2:$G$3 with:
| Date | Date |
|---|---|
| >=2023-05-01 | <=2024-04-01 |
But creating an Advanced filter fails with “This range does not contain a valid query”.
Have a look at your other topic.
I forgot to upload my new approach, that I described above…
As I almost use the same columns like the Data tab, Pivot table is maybe not the right tool.
Now I got it working with Advanced filter, but there are some drawbacks:
pivot_240704-1146.ods (292.6 KB)
- Changing the filter criteria doesn’t refresh the filter. I need to reset the filter and reconfigure it.
- The filtering only happens by hiding rows which means that the color alternation is broken. Unpleasant.
- As I don’t know how long the Data list will be, I need to reference a very long list for each new tab.
pivot_240704-1153.ods (279.8 KB)
Adding new tabs for new time ranges… I compared doing with with Advanced filters and also Pivot Tables.
Finally I still would prefer Pivot-Tables, as they better refresh without recreating a filter.
Pivot-Table drawbacks: No or only very basic formatting, no filter criteria read from table
Advanced filter drawbacks: Nice formatting but hiding rows breaks color alternation, no update on changing time range
I could go for Pivot tables and accept the very basic formatting. But how can I set the time range somewhere in the table for users that don’t know how to adapt the Pivot table filter?
I had working approaches using Pivot Tables and Macros.
One other requirement I dropped so far: It should also work on Excel 2019. I know compatibility reduces the feature set a lot…
Like Pivot Tables and Macros are not compatible.
I have a complete new approach:
Hours-Testproject_02.ods (61.7 KB)
Some things are still broken in Excel. I could either save as .xslx or using the .ods in Excel. Results look equally broken to me:
Opening .ods takes a minute in Excel.
Opening .xlsx is fast.
Incompatible is:
A3 text field showing the tab name
A1 text field showing the file name
C3 referencing the residue of the previous tab: =INDIRECT(TEXT(A3 - 1; “0”) & “.$E$4”)
Is there any more compatible approach?
https://wiki.documentfoundation.org/ReleaseNotes/24.8
Support importing and exporting OOXML pivot table (cell) format definitions, which allow direct formatting of the pivot table cells (which survive pivot table update). (Tomaž Vajngerl, Collabora)
Ok, this is still Version: 24.2.4.2 (X86_64) in my Linux distribution.
Next version, due next month, will support formattings within pivots.
Pivot was just one thing that wasn’t compatible.
I like my last approach, any way to get that working for both, also Excel 2019?
Should I start the same approach in Excel style and see if Libreoffice can deal with it? Is .xlsx the best shared medium between both?
As this is now more about compatibility with Excel, I moved the questions to Compatibility issues with Microsoft Excel 2019