[Solved] Pivot Table Redundancy

Discuss the spreadsheet application

[Solved] Pivot Table Redundancy

Postby AlexandraNyx » Wed Nov 13, 2019 6:18 pm

Question -

I make dozens of pivot tables, with the same column values, dozens of them too. All have different row fields.

Is there a way to make these pivot tables with the same column values without manually entering them in each time? Then enter the different row fields.

I hope so. Help!
Last edited by AlexandraNyx on Sat Nov 30, 2019 6:25 pm, edited 1 time in total.
OPEN OFFICE 4.1.1 ON WINDOWS 7
AlexandraNyx
 
Posts: 47
Joined: Sat Mar 21, 2015 9:13 pm

Re: Pivot Table Redundancy -

Postby Alex1 » Mon Nov 18, 2019 7:43 pm

Can you upload an example of the table, with sensitive data removed, so we can try to find an approach?
AOO 4.1.7 & LO 5.0.6 on Windows XP & 7
Alex1
Volunteer
 
Posts: 608
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: Pivot Table Redundancy -

Postby AlexandraNyx » Mon Nov 18, 2019 10:02 pm

Thanks for the attention Alex. It's a hypothetical table so any kind of table would apply. The column fields are all the same but with different row fields.

Instead of having to create new tables with the same column fields each time with different row fields is the another way to do it?
Thanks
OPEN OFFICE 4.1.1 ON WINDOWS 7
AlexandraNyx
 
Posts: 47
Joined: Sat Mar 21, 2015 9:13 pm

Re: Pivot Table Redundancy -

Postby Alex1 » Mon Nov 18, 2019 11:06 pm

Can you give the criteria you use to select the rows?
AOO 4.1.7 & LO 5.0.6 on Windows XP & 7
Alex1
Volunteer
 
Posts: 608
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: Pivot Table Redundancy -

Postby AlexandraNyx » Tue Nov 19, 2019 5:01 pm

Hi Alex
It'd be hypothetical.
In each new table, row fields would be different, with the same column fields.
I need different tables. Is there a more efficient way to create them than manually filling in the same columns each time.
Thanks partner
OPEN OFFICE 4.1.1 ON WINDOWS 7
AlexandraNyx
 
Posts: 47
Joined: Sat Mar 21, 2015 9:13 pm

Re: Pivot Table Redundancy -

Postby AlexandraNyx » Tue Nov 26, 2019 6:23 pm

to quote Def Leppard -
"Is anyone out there, does anyone care???"
I just gotta know!

Just foolin
OPEN OFFICE 4.1.1 ON WINDOWS 7
AlexandraNyx
 
Posts: 47
Joined: Sat Mar 21, 2015 9:13 pm

Re: Pivot Table Redundancy -

Postby Villeroy » Tue Nov 26, 2019 7:59 pm

Without seeing what you're doing, I don't understand the problem. Its always better to attach a sample file. Pivot tables can be complex.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot Table Redundancy -

Postby AlexandraNyx » Wed Nov 27, 2019 1:55 am

Hurray!

Someone is there.

Take any pivot table using 10 column values.

The question is. is there a way to make a second or a third pivot table, etc, using the SAME column values without manually putting the SAME values in each time?

The row field would be different in each different pivot table.
OPEN OFFICE 4.1.1 ON WINDOWS 7
AlexandraNyx
 
Posts: 47
Joined: Sat Mar 21, 2015 9:13 pm

Re: Pivot Table Redundancy -

Postby Villeroy » Wed Nov 27, 2019 1:57 pm

Lets take this pivot from another topic. The pivot on Sheet2 has values 2-9 in column field "TIER". The fields are derived from the source data. Every pivot table derived from the same source table with column field "TIER" will have the same column field values 2-9.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot Table Redundancy -

Postby Villeroy » Wed Nov 27, 2019 5:49 pm

If you want to filter the column field (for instance show or hide items 2,4,6,8) and sync this filter with other pivots, this is not possible without writing macro code.
You can merge pivot tables into one by adding more row fields and calculated data fields.
You can use a database and then build your pivots from the same filtering query. This might be the most elegant solution if my assumption about column field filtering is correct.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot Table Redundancy -

Postby AlexandraNyx » Wed Nov 27, 2019 11:25 pm

thank you my friend
you always come through
alexandra
let me absorb this prior to adding solved
OPEN OFFICE 4.1.1 ON WINDOWS 7
AlexandraNyx
 
Posts: 47
Joined: Sat Mar 21, 2015 9:13 pm

Re: Pivot Table Redundancy -

Postby Villeroy » Thu Nov 28, 2019 12:00 pm

Is my assumption true that you want to apply the same filter on column fields by means of the drop-down button besides the column field name?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot Table Redundancy -

Postby AlexandraNyx » Thu Nov 28, 2019 3:48 pm

I would like to understand how that works. Thanks. Specifically now I'm using the same columns in the data field. Like Column A through Column Z. In each pivot table, the Row fields would be different (ABC) or (ADF) or (EXZ) etc. What you're explaining could be a solution to something I hadn't considered. Thanks. Always open to learning new applications.
alexandra
OPEN OFFICE 4.1.1 ON WINDOWS 7
AlexandraNyx
 
Posts: 47
Joined: Sat Mar 21, 2015 9:13 pm

Re: Pivot Table Redundancy -

Postby Villeroy » Sat Nov 30, 2019 10:31 am

Please post a sample of what you have.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot Table Redundancy -

Postby Villeroy » Sat Nov 30, 2019 11:46 am

Download the attached files to your home directory.
Please call menu:Tools>Options>Base>Databases and register the downloaded database document as "PivotDatabase". This is required for the spreadsheet to use this database as a data source.
The attached database has a 3-column table "Data" with "Name" for the row fields, "Category" (letters A-Z) for the column fields and "Value" for the data fields.
A second table "Categories" stores the category letters you want to see in the pivots.
A query "Query1" shows coluns from "Data" matching column letters in "Categories".

The attached spreadsheet has a little input form to edit the category letters in the "Category" table.
When you refresh the 2 pivots aside, they will show the same column field values.
Attachments
SyncPivotFilter.ods
(22.13 KiB) Downloaded 3 times
PivotDatabase.odb
(18.31 KiB) Downloaded 3 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot Table Redundancy - I think "[Solved]"

Postby AlexandraNyx » Sun Dec 01, 2019 7:53 pm

Appreciate your knowledge my friend!
OPEN OFFICE 4.1.1 ON WINDOWS 7
AlexandraNyx
 
Posts: 47
Joined: Sat Mar 21, 2015 9:13 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 27 guests