GETPIVOTDATA - advice on how to use

Anyone familiar with GETPIVOTDATA? Specifically: 1) any way to pull data by referencing a date without having to depend on the way the date is formatted and 2) any knowledge of how efficient it is as a formula (vs. doing an INDEX-MATCH or other approach)?

Context: I’ve added a couple of tabs in my Foundation template to create an actual spend vs. budgeted spend analysis (similar to, but slightly different from the native Yearly Budget). To pull in actual historicals, I’ve got my entire transaction history pulled into a pivot table. I’m then using GETPIVOTDATA to aggregate spending by category by month. The formula I’m using to pull the data out into my actual-vs-budget comparison tab is: =getpivotdata(“Sum of amount”,‘Transactions Pivot’!$A$1,“Category”,$B7,“Month”,text(E$2,“m/d/yy”)). The issue is that if any transaction in my Transactions sheet somehow ends up with a date that not formatted m/d/yy then the whole setup won’t work correctly. So that’s what’s behind my first question. Second question is just whether this entire infrastructure could be done more efficiently. My sheet got pretty slow after adding this setup.

@benlcollins Heather at Tiller suggested you might have thoughts on this?

@jono or @randy do you know anything about GETPIVOTDATA?

Previously, @jonathan.altman was using the Budgets History sheet in the envelope budget tool to aggregate the actuals for this custom view of actuals against budget, but he’s transitioned to using the Foundation template and I’m not familiar with this feature of gSheets so pointed him here.

Otherwise, ideas on another way to aggregate the actuals?

@jonathan.altman it might be usefu/helpfull to share a screenshot of your dashboard :wink:

Hi @jonathan.altman,
I haven’t used the GETPIVOTDATA() function but I see it’s a way to create a pivot table by formulas rather than the more common method using Data -> Pivot Table and entering values in the pop-up boxes.

It’s a little hard to understand what you are doing without seeing the original pivot table you are creating. Can you share that or the formula you are using to create it?

I’m also not clear about the comment “if any transactions in my Transactions sheet somehow ends up with a date that not formatted m/d/yy”. So some transactions dates work but others don’t? Which dates don’t work? I’m not sure this would help but you might try text(E$2,"mm/dd/yy") instead of text(E$2,"m/d/yy").

Perhaps the best way I could help is if you explain what you want the report to look like? How do you want it different than the existing Yearly Budget report? Are you looking for something more like the Year-To-Date Comparison report? Year-to-Date Comparison Sheet - How To Use

In general, I find it’s more efficient to write formulas using QUERY() and SUMIFS() and VLOOKUPS() with ARRAYFORMULA() to build reports rather than using Pivot Tables. Pivot Tables have value for quick and relatively simple reports but you have more control with formulas.

Jon

Thanks for the reply. Your description of the GETPIVOTDATA formula isn’t quite right. You still need to have a properly built pivot table. It’s just a formula to pull a specific cell out of the pivot table results. My own post on this (vs. Heather’s) describes the issue, but if it’s not clear: I’ve built a pivot table. I’m using getpivotdata to pull data out of it. Both of those steps are simply to address the fact that I can’t come up with a better way to create the actual-vs.-budget analysis I’m trying to create. And the issue with dates is that GETPIVOTDATA doesn’t seem to treat dates as dates, but rather strings of characters. So if the formatting isn’t identical in the GETPIVOTDATA formula to what it is in the pivot table (which pulls in the entire Transactions tab) then the formula won’t work properly.

As for what I want it to look like, I tried to include a snapshot here, but can’t upload attachments. Please let me know the best way to share that with you. It’s mostly the same data as things like the Yearly Budget and other reports of that type, but less over-built than those. Just a simple spreadsheet of all actual and budgeted historical spend on a monthly basis.

Regarding formula efficiency, you’re saying that instead of a pivot table query, sumifs, vlookups, and arrayformula are less likely to cause laggy-ness in my Google Sheet than a pivot table? In my (Excel) experience, vlookup is one of the most processor-intensive formulas there is. But I’m the furthest thing from an engineer, which is why I’m asking the questions about laggy-ness in my sheet and pivot tables / GETPIVOTDATA to begin with.

Hi @jonathan.altman,
Thanks for the clarifications.

I think if instead of building the pivot table, you use formulas to make the intermediate table, you would have more control over the output and get the result you are looking for. This would make sure dates are treated as dates.

In case this helps you troubleshoot, note that Google Sheets doesn’t store dates in a mm/dd/yy format. It stores all dates as numbers. Today’s date 5/11/20 is stored as the number 43,962. Tomorrow is 43,963. When you give the cell a date format (or if Google does this automatically), you see the result like 5/11/20. If you change the date column of your pivot table to use the regular number format, I wonder if that will help troubleshoot the issue?

Maybe you could describe the final display you are seeking to achieve? What are the columns across the top? (Category / Budget / Actual ? ) What is the date range you want displayed? What’s in each row?

I haven’t fully tested the performance of pivot tables or GETPIVOTDATA vs other techniques. But formulas can usually fine-tune the results to get exactly what you are looking for. It sounds like pivot tables/GETPIVOTDATA is not able to do that for you. Let’s see if we can find the solution and then work on the efficiency.

Jon

See if you have better luck uploading the screenshot @jonathan.altman. I updated your settings so you should be able to now, it’s a security thing for new users.

Thanks @jono. I’m actually achieving my goals with the pivot table. The only two problems with it are 1) my concern about date format potentially breaking things (hasn’t happened, but easily could as I know Tiller sometimes changes date formats inconsistently within a given data set and without warning), and 2) efficiency given that my sheet takes at least 20-30 seconds (sometimes longer) to recalculate whenever I make a change of any kind.

Attaching a screenshot of the sheet

Ok. I’m glad you are achieving your goals.

On the date issue, I haven’t noticed any date problems myself. If there were date issues, a lot of things would break. I guess I wouldn’t worry about this myself.

30 seconds or longer delays does sound like a problem. My hunch is it could be the pivot table but I can’t be sure. Here’s some tips on how to improve sheet performance in general:

You might want to take a peek at the hidden formulas in the Yearly Budget and Year to Date sheets to see if they give you any ideas. Those sheets handle the calculations pretty quickly and only use formulas.

Jon

Thanks for the response, I appreciate it. I think you might not be understanding the issue I’m highlighting regarding dates. It’s not that dates are a problem, it’s how the GETPIVOTDATA formula in Google Sheets handles dates specifically, which involves how the date is formatted (Excel does it differently).

I’ll try redoing my tab using some of the formulas in the Yearly Budget tab if I can figure out how to do that. What I was hoping, before going through that major exercise, is that someone who understands how formulas, pivot tables, and other features in Sheets work could tell me whether one approach is intrinsically more efficient than another. Something in my Foundation Template is creating big lags. But I don’t know for sure what it is. Redoing my actual-vs-budget tab is a painful process to figure it out (especially since the pivot table may not be the root cause). But unless you have another suggestion for how to approach this…

As for the thread on performance: thanks for pointing me there. Seems to mostly be a confirmation that something about the way Tiller has things set up means a lot of heavier users are experiencing the same problems I am. Not very reassuring. Also makes me skeptical that removing my pivot table will fix things. (I’ve got 14,000 transactions.)

Hi @jonathan.altman,
Here’s what you could try.

Make a copy of your current spreadsheet. I’d expect that sheet would have a similar performance.

Then, try delete a single sheet, perhaps your Pivot table, and see what that does to the performance level. If there is no change, copy the sheet from your original back into the duplicate spreadsheet. Then try removing another sheet.

Some sheets are required for others to work. For example, you shouldn’t delete Transactions, Categories, Balance History, and Accounts. Most of those sheets don’t use many formulas and don’t cause performance problems.

Jon

Thanks, Jon. I tried that. As expected, it did help with latency (though there’s still a fair bit of a lag…but a noticeable improvement). The problem, however, is that removing the pivot table and the actual-vs-budget tab that was relying on the pivot table removes a core piece of functionality that I don’t want to lose. Suggestions? Is it, for example, possible to have a pivot table that’s driven off of a database in an entirely separate Sheet? If so, I could keep the analysis I want to do in a separate sheet and keep just the core Tiller Sheets in my Foundation Template to keep that particular file running more quickly.

Separately, but related: I also think trimming my balance history is likely to help, but I haven’t tried that yet as I don’t understand exactly what it’s going to do and whether I’ll risk losing important data. But I do have over 25K rows of balance history, and I know a large number of them are redundant.