Pivot table 'views'

[Chartist]

I have a very complex data source (80 columns, thousands of rows) and need different types of Pivot tables for analysis. I have created around 20 Pivot tables in different sheets based on the same data. What I wanted to know was, is there something like a saved 'view' which I can select in the Pivot table to generate different pivots in the same sheet (with different settings of rows, columns, etc but based on the same data)? Currently, to view the other Pivot, I have to go over to the other sheet - or customise the current pivot which doesnt make sense.

Thanks

 

[Fazza]

Hi,

It sounds like something that should exist but AFAIK it doesn't. (I don't know about Excel 2007.)

However, the same result can be readily achieved wth some programming.

To get the basics, the macro recorder will get you most of the way there. You might need to have some code to reset all filters, so that it would run first to restore the pivot table to its default set up before a particular custom set up. I posted something like that last week - so you should be able to search and find that. Maybe a post by me last week containing "pivot item". Or if it suited your requirements, you could have a master pivot table and every time you wanted a custom version copy from it and put the new PT on a new workbook. I guess even just create the custom PT from scratch, in VBA, every time. I think a VBA approach would be best.

How does that sound?

regards, Fazza

 

[Datsmart]

I would suggest looking at Advanced Filtering to do what you are after.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html

Another good link here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet.

 

[jeffreyweir]

One possibility would be to use the 'custom view' function with the raw data. You can't use custom views with tables (including pivot tables) but you can possibly use them on your raw data, combined with judicious use of the 'subtotal' command to provide summary statistics on the unfiltered rows.

I often use custom views to change filtering that applies to graph data series. So if I want my graph to only include data from particular clients or dates, then I change the selections I've made with the autofilter, and then save as a custom view. Check out http://office.microsoft.com/en-us/excel/HP012170451033.aspx or google for more info.

Otherwise advanced filtering would be a good way to go. There's a good example in John Walkenbach's Excel Formula's books called 'summarizing a list with a data table' that provides a good example. I use such data tables to filter large datasets and do stuff that you just can't do with pivot tables (as far as I know).

 

[Fazza]

Hi Jeffrey,

Can you give an example of the things that you can't do with a pivot table?

Regards, Fazza

 

[jeffreyweir]

It's hard to explain, and harder still to remember, but here goes.

I was analyzing electricity time-of-use metering for 11 different commercial sites. These meters spit out consumption data every 30 minutes, so I have 48 consumption 'trading periods' in the day, every day of the year, for each site. Approx 200,000 rows of data in total.

What I needed to do was plot coincident peak demand values so I could look for overall trends, and how use changes over particular half hourly trading period and a particular month. Cooincident peak demand means the highest combined load of a group of sites on a particular day at a particular time.

From memory, getting the max coincident peak demand in each trading period in a particular month was easy enough from the pivot table. I had to summarise the data by time period and month, and change the value field setting to max. But getting the average coincident demand was problematic, as the pivottable calculated averages from the disaggregated data (i.e. the individual sites) whereas I needed it to first lump up the data into daily blocks, then pick the average of all the days.

I could do this by pivoting the to aggregate it up across all sites, then copying the aggregated totals to a separate sheet so that I could then pick out coincident maximums and also average readings over time.

But with the data table, (i.e. database filtering to a table) and with a few subtotal functions I could get exactly what I wanted straight from the raw data, plus drop specific sites at will.

 

[jeffreyweir]

Or in simpler terms, I needed a pivot table to aggregate up some daily info, and then choose the max and average values from those aggregated totals. But I could only get it to provide averages from the disaggregated values.

 

[Fazza]

Thanks, Jeffrey. I might not understand fully, but it does sound like one couldn't get the result directly by going from the source data to a pivot table. Maybe it would be possible though if one did the aggregation as an extra step between the source data and the pivot table. This could be done manually via menu path data, import external data, new database query or choosing external data at the first step of the query wizard. Either way go into MS Query and do the aggregation there, so you might have fields for site, dates, time periods, consumption and you'd get the sums by day, or whatever. This goes into the pivot table - enter this summed field twice, once for maximum and once for average. How does that sound? F

 

[jeffreyweir]

Re your suggestion "Maybe it would be possible though if one did the aggregation as an extra step between the source data and the pivot table."

This was exactly what I was doing initially, however it was a pain if you wanted to exclude particular sites, or look at the electricity profile over a different period (say three months as opposed to one), or add new data into the database.

Also, I was charting the results, with different series for business days, non-business days, further differentiated into max and average. Which meant I had to filter the results by these criteria, then cut them one by one into a new worksheet so i could get them all on the same graph. Very labour intensive.

So the data summary table was a much better way to go, as you can change your criteria table on the fly and just hit refresh. And you can keep adding data to the database - as long as the range is dynamic then it includes the new data, with no intermediate steps needed. Only problem is it takes a long time to recalculate, given the summary table was 48 rows across (one for each trading period) and 365 down (days), with each cell within that matrix essentially holding an array formula to filter the 200,000 row database. When I run it, I go out for a long lunch! But the upside is that there's no intermediate steps between the database and the graph.

 

[Fazza]

This data table summary. Sounds to me like it could instead be a query table. Which can be parameterised - that is, linked to input cells, and refresh when they change.

I'm guessing a query table could do the summary for you in about a second instead of the array formula which, as you say, takes as long as a long lunch. This would be a non-code & non-formula approach though it sounds like it'd be better in VBA.

If you have provide the field names I can try to describe it. It would be interesting to see if it can do the job very quickly.