To create this pivot table is easy. (from the table on the left)
and then to group the result from the pivot table above, is also easy and give the following results.
My wish .:
Is it possible to be able to create both pivot tables on the same spreadsheet?
Theoretically, pivot tables are enumerated on a spreadsheet, so it might be possible using code, but I don’t see how to do it with the UI. I added a second pivot table using code, but I do not see how to update the settings in the object returned by createDataPilotDescriptor, for example.
Of course, you can build a dashboard that just reflects the data from different sheets. I figure you had thought of that, but I’ll attach an ODS that has a tiny macro to put on the dashboard for refreshing the data.
Option Explicit
Sub DoublePivot()
Dim Sheet As Object
Dim Pivot As Object
Dim Table As String
For Each Table In Array("P1","P2")
Sheet = ThisComponent.getSheets().getByName(Table)
Pivot = Sheet.getDataPilotTables.getByIndex(0)
Pivot.refresh()
Next Table
End Sub
DoublePivot.ods (13.9 KB)
Excuse me!
Sees that my example does not give the desired result in the pivot table.
Yes! the result seams to be OK!
But, there is not good to having to add a code to solve the task.
(Thanks - Excuse me!)
Sees that my example does not give the desired result in the pivot table.
(clarifies me in the next answer)
Nice solution!
Can I do the same?
To get the correct count / result I want in the pivot table, there probably must be a sum column in the source table. 
Like this example (the second pivot table has been pasted to show my desire)
A simple source file .:
Pivot table in LO - desired source.ods (17.2 KB)
Yes.
And what doesn’t work out?
Pivot table in LO - desired source (2).ods (17.1 KB)
A small macro updates the tables when the source data changes.
Only there should be more than 1 element in the group, otherwise, as it turned out, a single element of the group is added to the previous group (e.g. 3000 in your file). I changed the number of one group in the data.
NOTE: And how I grouped it, I don’t know myself. Created a named data range. The first pivot table is associated with the range reference, and the second is associated with the range name. Both tables display the same source… And so, both tables started behaving independently.
Unless that was your problem.
Thank you!
From the beginning!
.
The following can be seen on the screen (Swedish LO - sorry)
.
Creating another pivot table in the same way is not difficult.
The result look like this .:
The next step will probably be to group the lower pivot table.
(and create groups 1000-1999, 2000-2999 and 3000-3999)
Like this .:
.
The problem is that the grouping affects both pivot tables - with this result .:
I have no idea why or how to handle pivot tables with the same source.
This is the source file .:
LO - Test dual pivot tables.ods (13.3 KB)
Thank you eeigor!
Your solution does not works as desired, for me with Windows and LO v7.1.5.2 svedish. (which I usually use)
When I create my own pivot table, it is created, but the pivot table does not become unique.
(the pivot table 1 and 2 is also changed at the same time as mine)
This did not happen Open Office.
Am a little split - do I have to start using Open Office - just for this?
Don’t be split/torn on this. I understand the frustration, but we are dealing with computers here. Why is code a bad thing? Is having to use a 10-line macro to do the refreshing a reason to jump to OO? Does your firm prohibit macros? What if you have multiple pivot tables on an OO sheet and make a judgement based on data from a table that is not properly refreshed?
If you build a dashboard sheet with an easy way to refresh all pivot tables via a tiny macro, you have the confidence that all data are synchronized. You can also apply pretty-printing to impress boss or client, something you can’t fully apply to active headers on pivot tables. And you don’t have to worry about active headers printing awkwardly on hard copies.
Actually, according to Lupp’s Laws, raw data, data handling, and reporting should each be on separate sheets, in any case. Again, I see the frustration, but perhaps it is a blessing in disguise, leading to a separate report sheet.
I think not.
But no one has answered my questions yet.
However, I can offer you another way instead of the first pivot table (where the data is not grouped). This is a substitution table or a table with one or two inputs (menu: Tools >> Multiple operations).
Thanks!
All codes have restrictions.
If the code is not general - ie. will work in all situations, the credibility of the result will decrease.
Since I am not good at macro programming / macro handling in LO - I have a hard time reading where the restrictions are or changing the code to suit my wishes. (sometimes the solution works, other times not)
Since the raw data is sometimes copied into a spreadsheet, I do not know if numbers have the text or number format. Only that’s a problem.
Very recently I learned to use ctrl + F8 - Shows colors depending on cell format e.g.
But it would have been best to be able to select a column and indicate / change so that all numbers in this column have the number format.
When do I know that the macro is not working?
In this case, the example has only a few rows and columns.
But in my real data I have many columns (8-70) and many rows (50-45000)
I decide
But we have many computers and few “computer-savvy” people
Many of the employees are “afraid” of computers.
Have previously solved the problem manually and with formulas, but thought to facilitate the handling for me with pivot tables.
I’m not yet sure that my wish can be fully resolved with pivot tables. ( but a lot of time can be saved )
Have encountered two problems - the handling of pivot tables - but also the layout of the results. (which I have not touched on at all)
Since there are many rows and columns with different contents to handle, it is important to be able to put units on the columns.
But I feel that is another challenge.
Because the data is static in this case, cell updates are not a priority. (But there are other cases when it would have been good …)
The right structure!
What question?
When I change and move values in the test spreadsheet, it seems to work.
But when a new spreadsheet or / sheet was cretated (LO) I get nothing to work (maybe I can’t create the pivot table on right way)
As previously stated, real-time updating is not a high priority on.
My wish is to create two pivot tables with different structure from the same data. (it works in Open Office - even if it was a bit of a “trick”).
Since I learned how to do in Open Office, I have not encountered any problems creating the pivot tables in OpenOffice and opening the results in LibreOffice. (but it felt like a detour).
Creating a new PivotTable in LibreOffice on the same spreadsheet from OpenOffice, and updating them, the PivotTables from OpenOffice are changed.
Yes, the question suggests itself. When do 2 pivot tables linked to the same data source behave independently? I have implemented this type of work, but the way to get it looks unconvincing.
The data source of the first table is a reference to the range, and the source of the second table is a named range with the same address. It is essentially the same thing (?). But they work independently in LO Calc v7.2.
Edit:
Well, if you have such large data tables, then set the size of the data range with a margin: 50000 (rows). The trick: for the 1st pivot table take 49999 (rows), and for the 2nd - 50000 (rows). The ranges are different, although they process the same data, the difference is only in the number of empty rows under the data. And everything will work as it should. Try my example.
Thank you for your commitment and time!
It seems like you can handle pivot tables (and LO) very well.
It seems simple when you describe the solutions ( But for me it doesn’t work. )
Has upgraded LO to 7.2.5.2 Swe - but experiences no change with pivot tables.
The last example worked well as long as I did not change too much (added columns / change headers etc.)
Probably need to change the macro.
Yes - it will surely solve exactly the configuration I am working on right now (but yesterday I got a different structure …)
Failed to create a pivot table with my own structure.
Still wondering if it’s a bug in LO because I get it to work in OO. (or is it still my knowledge that is not enough)
Started a new question about, among other things, layout and pivot tables - Right now I think it will be “no” to all questions - but we’ll see. Calculations and layout
Found one of the problems I did not understand - about Group pivot tables, the field to be grouped must be a numeric field (or date).
Freely translated from the LO manual - Group pivot tables
Depending on the format of the selected cells, either a new group field is added to the pivot table or one of the two groups dialogs, numeric or date values, is displayed.
My wish was to get the “gruop dialog”.
