Hello, folks.
I have a bunch of pivot tables where I need to have color scale formatting for better experience. Unfortunately, any color scale formatting that was applied either directly to pivot table or to source table vanishes on refresh of pivot table data. Is there any way to avoid this?
Libreoffice 7.3.6.2, 7.4.1.
Can you upload an ODF (.ods) type sample file here?
test-doc_Zizi64.ods (25.1 KB)
My first tip:
Use helper cells for the colorized display, what will reference to the results of the working Pivot table.
I think that this can only be done with a macro.
Sorry, but seems like I don’t get the idea. I downloaded your file, opened it, opened sheet with pivot table, clicked refresh on the PT and color formatting vanished. Should I’ve done something before refreshing? Thanks.
It’s a pity. Hoped I somehow could avoid a lot of manual work.
In the attached example, after each update of the pivot table, conditional formatting (color scale) is set.
Formatting is set only for those data fields whose names begin with a space.
Unfortunately, due to the presence of a tdf#119590 bug, setting the color scale is implemented in a long and confusing way.
test-doc2.ods (31.3 KB)
Not ideal, but can work, creating a second sheet with the formats pointing to the pivot table with the values.
test-doc.ods (27.5 KB)
Yeah, it works, thanks. Still it’s just a workaround that brings more freezes to my spreadsheets.
Why should a few additional simple formulas cause freezes?
Cause in my case I use multiple PTs in documents with over 10 000 cells in use with values, text and quite complex formulas. Even just PTs makes this document laggy when working with it. When I add multiple additional PTs just to keep color scale it makes this document barely workable (up to 3 seconds to open cell). I’m not whining, it’s just a fact I face when work in LO. It’s not yet as good in terms of performance as MO.
Seems like I find a balanced solution. I stopped use of PTs. Instead I created manually tables-analogs for all PTs. Yeah, it took some time and will take time when I need additional tables, but at least it works and doesn’t affect performance that much. I hope LO will implement more performance and flexible solutions for PTs in the nearest future.
Without knowing the details I can’t analyze anything, but there is a faint suspicion that the design of your “complex of sheets” might be not well enough considered. 10000 cells is just a farthing (a single “classic” column has up to more than 100 times as many), but complex formulas may be easily simplified in many cases. One kind of simplification often can be to use additional cells or ranges as helpers.
Another point: For better efficiency CF conditions are only recalculated for cells in the currently viewed range. For CF of one of the All Cells variants this may, however, require a kind of statistical evaluation for thousands of cells. I personally avoid such gimmick.
As a user of “ColorScale” CF you are probably coming from Excel. That may be bad training. Excel isn’t software, but a “product” for sale. (Well, StarOffice also once was developed with commercial considerations in mind.)
I spent some time to consider the basic situation.
Should I bring the formats to the original pivot table, or should I move (copy) the data to a “perfectly” formatted range?
The cumbersome fact for what both ways don’t work without some overhead is the changing number of output rows. Generally I did not try to cover all the possible cases.
Howevber, for a reasonably large space of use cases an attempt bringing the values and the formats together in a third place by user code seemed to be promising.
You may play with the attached example. Please note that there is all but a final solution. Lots of checks aren’t included, and some special cases (less than 3 value-rows e.g) aren’t allowed.
persistentDataPilotTableFormatting.ods (46.8 KB)
@sokol92: Sorry! Actually I thought I did, but obviously I didn’t get it correctly. Clever solution!
However, though my “solution” is more complicated in a sense, I won’t delete it. Its additional features concerning also non-conditional kinds of cell formatting and column formatting don’t fit exactly easily into your scheme. As far as I see it currently, you might have to define and use a lot of cell styles, probably, to get the same functionality. On the other hand, there is the Filtering…
and I didn’t thoroughly consider the cases of additional,row-fields and of variable number of columns.
Sorry.
It’s good to be able to look at the problem from different angles! I always find something new and useful in your answers.
Format with Format| Conditional Formatting and after refresh you have to go again to Format| Conditional Formatting select one of these formatting of the needed table, press
twice OK and you have your color format again. Libre Office should make this automatically like Excel.