Pivot table cells lose format when refreshed

[garabombo]

Back to using Calc (unfortunately at work I’m stuck with Excel) for a personal project.

I format the cells of a Pivot Table (as example as Number with 2 decimals) on Average Data Fields.
Every time I refresh the Pivot Table this format is lost.

I understand that this was already found in 2019 (Calc - pivot table looses its column and row formats on refresh) and dismissed as WAD, but I would like to know if somebody else finds this useful or not.

I would love to keep at leat the cell formatting, or adding like in Excel the option to format the Data during Pivot Table creation.

[Villeroy]

The data fields of a pivot table inherit number formats from the source table.

[garabombo]

@Villeroy thank you ! That helps in a way. But in another not really much.

If the source is Number (no decimals) and the Pivot does the Average, this Average will be Number (no decimals).

I understand the reason behind it, still think that being able to select Average - Number, 2 decimals in the properties of the Pivot would be great. Kind of like Excel does. If you want, you can use it, if not the default behaviour is kept

[Villeroy]

Works for me.
t74515.ods (17.4 KB)

[garabombo]

Yes it works perfectly. My doubt remains anyway.

If I have the source that is whole numbers (so no decimal) and I do Average in the Pivot, I’d have whole numbers there too (so no decimals again). But as Average, you want to have decimals most probably. So the need (or wish) to have the ability to format the pivot remains.

I agree that it’s a minor inconvenience, but it could maybe be a feature.

[Villeroy]

In my demo file t74515.ods I calculated the averages of integers and they are displayed as decimal fractions.

[garabombo]

Sure, it works, as I said before.

But my wish remains: the possibility to format the pivot table result and keep that format.

Because in your file the average is has as many decimals as the result of the average. I would like to be able to set it up via interface to 2 decimals (as an example) and I don’t see how it is possible to do it.

[Villeroy]

Why do you need to format integers? Either they are integer, then you don’t need to specify and decimal digits, or they are not, then you should not lie about the true values by cutting off decimal digits.

[garabombo]

I don’t need to format, I want to format.

I understand that it’s working, this is not a problem of if there is a bug or not.

This is a new feature that I believe would add value to the product. The data is good, you can present it in different ways. I believe that having the option to format it directly in the pivot table (in the Properties of the pivot table) and that this format is kept between refreshes could be “cool”.

As in your example, there are averages that return 2 decimal points, others none, others 5. This is not a nice way of presenting data. The “nice” way is to have all your data with the same set of decimal points. Of course there is a workaround. What I’m saying is that this is a feature. A feature that a direct competitor has, so maybe it would be worth adding.