Need help with GETPIVOTDATA

[RDaga]

I have been working on a report and have been learning as I am doing it. Now I am stuck at a point and not able to figure out the solution so thought of reaching out for help.

In this report I am using a Data Model Pivot in Sheet 2 and on sheet one I am using GETPIVOTDATA to populate the data.

Cell A2 has date which is coming from a link to another cell
=IFERROR(IF(Pivots!BE6="Grand Total","",IF(Pivots!BE6=0,"",'Data Model'!AX4)),"")

Cell B1 has hard coded time as (0,1,2,3...... 23)

In Cell B2 I am trying to run the GETPIVOTDATA and it is showing the result, but as soon as I make it ref to cell A2 to get the date it is throwing an error.

=GETPIVOTDATA("[Measures].[Count of Incident #]",'Data Model'!$AZ$3,"[Range 1].[Time]","[Range 1].[Time].&["&B$1&"]","[Range 1].[Date]","[Range 1].[Date].&["&VALUE(A2)&"]")


Please help.

PS. Will not be able to share the file as has office data.

 

[AlanSidman]

in lieu of your actual file, suggest you mock up a sample file that is representative of your actual file with dummy data. Much easier to work with some data in your layout than try to recreate from your dissertation.

 

[RDaga]

Thank you Alan for the help. I have tried and created a test file. Please find the same attached.

 

[AlanSidman]

Does this look like what you want? Open the file to see the entire range
Data Range

	A​	B​	C​	D​	E​	F​	G​
3​	IncidentNr​	Time​	​	​	​	​	​
4​	Date​	1​	2​	3​	4​	5​	6​
5​	9/1/2020​	5696 ,5830​	5901​	​	​	​	5696 ,5830​
6​	9/2/2020​	5899​	5823 ,5900​	5834​	​	​	​
7​	9/3/2020​	​	5555​	5824 ,5831​	5835​	​	​
8​	9/4/2020​	​	​	5828​	5752 ,5832​	5836​	​
9​	9/5/2020​	​	​	​	5829​	5753 ,5757​	​
10​	9/6/2020​	5833​	​	​	​	5756​	5825 ,5833​

 

[RDaga]

Does this look like what you want? Open the file to see the entire range
Data Range

	A​	B​	C​	D​	E​	F​	G​
3​	IncidentNr​	Time​	​	​	​	​	​
4​	Date​	1​	2​	3​	4​	5​	6​
5​	9/1/2020​	5696 ,5830​	5901​	​	​	​	5696 ,5830​
6​	9/2/2020​	5899​	5823 ,5900​	5834​	​	​	​
7​	9/3/2020​	​	5555​	5824 ,5831​	5835​	​	​
8​	9/4/2020​	​	​	5828​	5752 ,5832​	5836​	​
9​	9/5/2020​	​	​	​	5829​	5753 ,5757​	​
10​	9/6/2020​	5833​	​	​	​	5756​	5825 ,5833​

Thank you @AlanSidman for the help. It was my bad that I did not add the sample output
Basically there will be a slicer which will have people name and as we select the name it will automatically pull the date from the pivot and based on that will populate the date and the count per hour.