• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with GETPIVOTDATA

RDaga

New Member
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.
 
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.
 
Thank you Alan for the help. I have tried and created a test file. Please find the same attached.
 

Attachments

  • Test.xlsx
    94.8 KB · Views: 2
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​
 

Attachments

  • Test.xlsx
    155.1 KB · Views: 1
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.

71280
 

Attachments

  • Test.xlsx
    150.6 KB · Views: 1
Back
Top