Master pivot table with multiple smaller pivot tables

[AngieNaude]

Hi all,

I am not sure how to give this query a title so sorry for that. I hope this post finds itself in the right place.

What I have is a data set of all sales and I have to run a report each week. It has columns of posting date, customer names, product names, quantity, cost, sales etc. And it is for a comparison of what happened last year to this year. Further to that, we must report on month to date last year vs. this year to the ACTUAL DATE and not a full month view. So MTD Mar 2022 vs 2023 is, 1-20 Mar 2022 vs 1-20 Mar 2023. This is where it gets tricky.

I pull ALL the data from 1 Jan 2022 to date (which is 20 Mar 2023). I can filter on posting date to do the comparison as if I select in the filter multiple dates which would be 1 Mar - 20 Mar, this will show me that data for 2022 and2023. It saves time pulling multiple date range data.

My various pivot table show different views like Quantity by Customer, Quantity by Product, Retail products etc. And all on different pages. Now, each of these pivots have that filter tab with Posting Date. So when I refresh the data since last week say, I have to go and add the additional days in the filter. But I have to do this for ALL of the tables. There are maybe about 15 tables in this sheet. I have to go to each table and select the next 7 days. Not that it is hard to do but I do worry I miss one then that data is wrong.

Is there are way of creating multiple tables with different views (different fields/info) that have the posting date as a filter from a master pivot, and when you update the filter if the master pivot, it automatically updates all the little tables but does NOT change the fields and views?

TIA

 

[chris smith5]

Hi AngieNaude,

Here are the steps:

1. Create a master pivot table that includes all the fields you want to analyze, including the posting date.
2. Create each of the little tables with different views (different fields/info) that you want to see, but do not add the posting date filter to these tables.
3. In each of the little tables, create a formula that references the posting date filter in the master pivot table. The formula should reference the cell that contains the filter value in the master pivot table.
4. Whenever you update the filter in the master pivot table, all the little tables will automatically update based on the filter value in the master pivot table.

Here is an example of what the formula might look like in one of the little tables:

=GETPIVOTDATA("Quantity",$A$1,"Posting Date",$B$1)

In this formula, "Quantity" is the name of the field you want to analyze, $A$1 is the location of the master pivot table, and $B$1 is the cell that contains the posting date filter in the master pivot table.

By referencing the posting date filter in the master pivot table, you ensure that all the little tables will update automatically when you change the filter in the master pivot table.

Let me know if this worked.

Best Regards.

 

[AngieNaude]

Hi AngieNaude,

Here are the steps:

1. Create a master pivot table that includes all the fields you want to analyze, including the posting date.
2. Create each of the little tables with different views (different fields/info) that you want to see, but do not add the posting date filter to these tables.
3. In each of the little tables, create a formula that references the posting date filter in the master pivot table. The formula should reference the cell that contains the filter value in the master pivot table.
4. Whenever you update the filter in the master pivot table, all the little tables will automatically update based on the filter value in the master pivot table.

Here is an example of what the formula might look like in one of the little tables:

=GETPIVOTDATA("Quantity",$A$1,"Posting Date",$B$1)

In this formula, "Quantity" is the name of the field you want to analyze, $A$1 is the location of the master pivot table, and $B$1 is the cell that contains the posting date filter in the master pivot table.

By referencing the posting date filter in the master pivot table, you ensure that all the little tables will update automatically when you change the filter in the master pivot table.

Let me know if this worked.

Best Regards.

Hi Chris, thanks for the reply. Are you speaking of normal little tables where the data just references the data from the full Pivot or actual little Pivot tables from the main Pivot with different views? If just normal tables, there is a huge range of data that one small table has to reference so I don't think that would work. The main data has 2500 rows with about 20 columns not including gap columns for graphs.

Also it is not a specific date that I am looking for but a date range. So say for March, the last report I pulled, the posting date filter ticked all of the boxes between 1 Mar and 13 Mar. Running today's report I need to go and select 7 more days from the last report.

 

[rcgyuk]

My solution to this was two add two columns in the data, called Mth and YTD. These are formulas and give either Y or N. These are what I filter on. So if the data has extra line for new dates in the month, all you need to do is refresh the pivot(s)