Modify GetPivotTable formula for a Specific Date or Date Range?

I've been looking around for a solution for this, haven't found exactly what I am needing. Is there a way to modify the GetPivotTable formula to show a specific date?

My formula looks like this

Please Login or Register  to view this content.

where 20131101 is the date to be modified

Can something be embedded like today() or today()-1, etc? Ideally, I would like to sub in different operator IDs (the Operator ID in the above formula is 555) and get data for that operator on a specific date, and maybe eventually a date range.

Working with a large spreadsheet, so this solution or something similar would be a great help.

Thanks for any help!

Thanks, Rory:

I tried that but am getting a ref error. I tried "insert function" as well, and it doesn't seem to be working.

I'm working with limited access to the pivot table currently. I think that the table is not fully displaying all the
data for all the available dates and that's why I am getting the ref error? Or will this work even if the data is not visible in the pivot table as long as there is a working connection to the server? I will try this again when I have full access.

Thanks, again.

Rory (and anyone that can help):

I've tried the formula and ensured that the date and data are visible in the pivot table that
I am trying to display data from. I still get a ref error. If I do a simple =getpivottable function
and click on the cell data on the worksheet that I want to retrieve the info from, it works. But since
I have over 300 records to do this for, with more to come, I was hoping I could copy this formula
for each and eventually make this a macro.

Is there another function I should try or reference material to look into? I've looked at contextures

HTML Code: 

But I am trying to get away from putting the actual
date in.

Thank you

@JosephP

I kept the syntax the same, but had to modify the "Company Name" portion. I used that generic
term for sensitive info.

Update: I altered the today() portion to today()-1 because the server hasn't updated totals for today (I am thinking).
When I implemented that, I got the data to populate for yesterday's date. This is optimal because of the nature of the
KPI's I am analyzing. So, thank you Rory (and JosephP) for taking time out to help!

And I am wondering, what is the syntax for getting totals for week-to-date and period/month-to-date? I've looked around and didn't see anything of this sort (just saw things like =(NOW()+1)+(WEEKDAY(NOW())=6)*2+(WEEKDAY(NOW())=7), would this work or is it that I would have to point my cells to corresponding subtotals on the source data sheet?

Thanks!

Thanks Rory for the response!

I tried the formula you suggested and got an invalid argument error. I corrected
this by omitting a parenthesis (see bold text)

Please Login or Register  to view this content.

However, I also change the "A1" and "A2" because the week start date is in E16 and the end date is in E22 with all the dates in between (i.e. E16=2013-10-28, E17=2013-10-29, E18=2013-10-30, E19=2013-10-31, E20=2013-11-01, E21=2013-11-02, E22=2013-11-03)

Well, all the cells I tested this out on are returning the iferror 0. Even the ones that I know have data in them. What have I goofed up on?

Thanks!

@JosephP: this is what is displayed in the cells

E16: 2013-10-28 00:00:00
E22: 2013-11-03 00:00:00.

Is that what you are asking? This is from my pivot table (from a cube) sheet.

Thanks!

Just checked. No, it doesn't.

Possibly (really, really) stupid question: but is there a way to find the "real" dates on this pivot table?

I've attached a screenshot example of what my pivot table looks like. It's pulled from a cube.
I am trying to get the current week and current month/period to automatically populate (using a formula-hopefully!) in another worksheet, like I was able to with the "today" function (thanks to you and Rory!)

=GETPIVOTDATA("[Measures].[% Oper KPI A]",$A$3,"[Operator].[Operator]","[Operator].[Operator].[Operator].&[4323]","[Company Name Date].[Company Name Date]","[Company Name Date].[Gompany Name Date].[Date].&[20131103]")

I tried it both ways (CSE and without CSE) I get an Excel formula error and when I hit "ok", this is highlighted (see bold):

=SUMPRODUCT(IFERROR(GETPIVOTDATA("[Measures].[% Oper KPI A]'Data Cube 1'!$A$3,"[Operator].[Operator]","[Operator].[Operator].[Operator].&[555]","[Company Name Date].[Company Name Date]","[Company Name Date].[Company Name Date].[Date].&["&text(ROW(INDIRECT(A1&":"&A2))),"yyyymmdd")&"]"),0))

Rory and JosephP:

It's returning the iferror 0's again for employees that I know there should be actual data. I'm poking around with it, but so far it's a no go.

I will try to, but I'm afraid it would take a lot of time to hide sensitive data (and you've seen from my previous posts what a beginner I am). I checked the connection properties and it is not set to refresh data upon opening, so that's good.

I truly appreciate you both helping me with this! I have been frustrated and working under a deadline and this is the "last piece" before the next phase.

@JosephP (thanks a ton):

If I understand this correctly, I need to specify/create the start and end dates somewhere on my summary sheet? That's the
only missing piece? Or do I point to the specific dates already listed on the pivot table sheet?

Could you show an example of hardcoding the dates in the formula? That would be ideal since the syntax/structure of this formula will have to be applied to many, many records.

Thank you, JosephP and Rory! Your help is greatly appreciated! I think I am
understanding enough about this function that I can apply it to the month/period
date range as well.

I really appreciate you both walking me through the steps and answering my questions!

Hi again, Rory and JosephP (and whoever can help!)

How would I modify this formula to give me the average of these values over the specific time period (week, or month, etc)? Would I incorporate averageif? If so, where? When I attempted to, I got "too few arguments" error.

=SUMPRODUCT(IFERROR(GETPIVOTDATA("[Measures].[KPI A]",'Data Cube 1'!$A$3,"[Company Date].[Company Date]","[Company Date].[Company Date].[Date].&["&TEXT(ROW(INDIRECT(AB5&":"&AB6)),"yyyymmdd")&"]","[Operator].[Operator]","[Operator].[Operator].[Operator].&[555]"),0)

Thank you very much for any help anyone can give!

@JosephP,

Thanks so much for responding! So far, it's not working (surprise, surprise) I get a value error. I am sure it's something I've done or didn't do. I've done a simple average on some pivot table data and that seems to work with no issue. It's just using this "getpivotdata" syntax for the specified date range that seems to make things dicey. I'm using Excel 2013 if that gives any extra insight.

Thanks, again

@JosephP:

The good news is, I finally got the formula to work. The challenge I now have is that I have just been informed that the data
that is needed is not a "true" average. Meaning that there are behind the scene calculations done on the server (I'm guessing).

I think I need to close this topic as solved and do a search or open another topic on just retrieving the data using some kind of modification of this formula:

=GETPIVOTDATA("[Measures].[KPI A]",'Data Cube'!$A$3,"[Operator].[Operator]","[Operator].[Operator].[Operator].&[555]","[Date].[Date]","[Date].[Date].[Week].&[2013]&[11]&[3]")

where &[2013]&[11]&[3]") can be modified depending on what the current date is. At this point, I just need this worksheet to refresh with the current data (week-to-date, period-to-date) for the KPIs that are displayed in the pivot table from the cube (if you have any ideas or insight, please feel free to let me know.)

I really appreciate your help (and have starred you accordingly.)

Thanks again