Hallelujah! =GETPIVOTDATA

[dan_l]

Lemme just say this:


This is probably the most bad ass formula in the history of bad ass formulas.


Basically: I've been doing dashboardy things with these byzantine set ups----using vlookups, index, indirects as the engine for the dynamic aspects of the worksheet. So this past weekend I spent a half an hour learning a little bit of =getpivotdata, and....absolutely, totally, completely faster, more powerful, and more effective than the previous method. Literally, this morning over a cup of coffee I was able to throw together a crappy (not very insightful, but good proof of concept) comparison of 2 baseball teams in 10 year increments for about 80 years worth of data (pro-baseball reference if you're curious)

 

[keymaster]

Good to know that. I have tried to learn GETPIVOTDATA few times earlier, but gave up in the middle. Can you share few points where it helped you and how?

 

[dan_l]

Sure. Give me a couple of days.


Incidentally, I have to email you.

 

[dan_l]

So here's how it helps:

-All the advantages of Pivot Tables: groupings, calculated fields, totals, subtotals---whatever. This substantially reduces the time in my shape phase.

-Presentation phase: I like being 'dashboardy', but it takes quite a bit of time. As I mentioned, normally I hard set it with vlookups, indirects, other stuff that typically requires quite a bit of set up both in the formulas and in the way you lay in the data.


Here's how I got started:

1. Got data

2. Made a really simple pivot table

3. Went to another worksheet, hit '=', went over to the pivot sheet, clicked randomly, and started screwing around. Seriously: it's that easy.

4. Using some data validation,


I know this isn't anything all that impressive for your average Chandoo type, but for me it drastically reduces the time required to do some rather cool stuff. I sort of envision making use of this for one of my ever growing list of reports that I have to do at work.


If anybody is like I was 2 weeks ago and thinking it would take a while to learn this well enough to do something useful with it, here's a quick POC of it:


http://dl.dropbox.com/u/1275899/cubssoxtwinscards1.xlsx


Quick disclaimers:

1. This was quick, I put no polish on it.

2. The charts suck, I know.

3. Teams and Era are data validation. There's a total of 4 teams, but only room to display 3.

4. You can peek at the original data. I spent exactly 0 minutes with it, which is pretty impressive. Prior to knowing a little bit about =getpivotdata, I'd probably had to have invested a little time in shaping that data up. It's just the straight csvs from http://www.baseball-reference.com/

5. You'll notice that the White Sox own all of you.


The only thing that I can't figure out thus far:


=GETPIVOTDATA("Wins",$A$3,"Year",2009,"Tm","Chicago Cubs","Year2","2000's")


"Wins" = Value field

A3 = is basically just the address of the pivot table as far as excel is concerned

Everything else = just the triangulation points that is used to look up the data.


"Everything Else" is dynamic. I can manipulate that by referencing other cells.

So where b54 contains Text "Chicago White Sox", the formula =GETPIVOTDATA("Wins",$A$3,"Year",2009,"Tm",b54,"Year2","2000's") returns the number of wins for the Sox in 09.

Which is fantabulous.


But I can't seem do do the same with the Value field. I've tried pointing it to B54 where B54 contains:

Losses

"Losses"


So I can't seem to manipulate that with a reference. If anybody has some ideas, do lemme know.

 

[dan_l]

Update: the Value field can be changed dynamically by doing a concatenate of the cell with a "".


I knew it musta been something like that.


=GETPIVOTDATA(<b>C70&""</b>,$A$3,"Tm","Chicago Cubs","Year2",2010)

 

[carics]

That's great! Good teaching and examples also.


Thanks a lot

 

[Collock]

I do this with summary charts all the time. The real problem with using this for Dashboards is filtering data. For example if I have a date filter in my pivot, I cannot use getpivot to change that filter. You could write a VBA function that would adjust the filter, however if different elements of the dashboard need different filters this would not work. You would need to have a another pivot table and program it so the information would refer to the correct pivot


So this works for some very simple dashboards, ones with limited functionality, or static dashboards. However even with static dashboards in some cases for it to work correctly you need to create multiple pivot tables if you have data which needs different filters. Also, I found Excel has some stability issues when working with multiple pivot tables, and performance issues, especially if charts are linked to the pivot tables.


I personal have on my list to learn MS Query and brush up on my SQL. Not sure about its performance but I think SQL is really is the way to go for most types of analysis which require complex filters/sortations in excel, presentations, and dashboards over using vlookups, index, indirects, complex if statement strings, and pivot tables. Pivot tables are great for simple analysis and cover 90% of data summaries I need to do. But thoughs cases were I need to combine multiple pivots or complex sortations I think SQL is way more efficient and quicker to perform.

 

[dan_l]

Please share your implementation method!


I use msquery quite a bit, but I've never been able to figure out how to run it 'live' with a spreadsheet.