Results 1 to 10 of 10

Thread: GETPIVOTDATA with multiple data_fields

  1. #1

    GETPIVOTDATA with multiple data_fields



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I'm new to this forum, and I'm hoping you can help me with an issue regarding the GETPIVOTDATA function.

    In Excel 2010 I've organized some sales data by month, product and country. I've started using the GETPIVOTDATA function to pull monthly year to date sales data, and after quite some investigation I found the syntax for selecting multiple data fields.

    This is an example of the parameters I'm using when I want to see the total sales for January, February and March 2011 for country DK.

    Data_field: {" Act 01.11";" Act 02.11";" Act 03.11"}

    Pivot_table: DataPivot!$A$6

    Field1: "Country"

    Item1: "DK"

    Then my formula looks like this:

    SUM(GETPIVOTDATA({" Act 01.11";" Act 02.11";" Act 03.11"};DataPivot!$A$6;"Country";"DK")

    The above formula works fine, but I can't find the correct syntax to get the value for the data_field parameter from another cell, if I need multiple values. It works fine with single values for example like this:

    SUM(GETPIVOTDATA(" "&Parameters!$A$1;DataPivot!$A$6;"Country";"DK")

    where Parameters!A1 = Act 01.11

    So I need to know what content I should have in Parameters!A1 and what the syntax in the function GETPIVOTDATA should be if I want to get multiple data fields, for example {" Act 01.11";" Act 02.11";" Act 03.11"}.

    I've tried in all kinds of ways over the last few days, but with no luck. I think the tricky parts are the {} and the fact that there should be several quotes within the sentence.

    I hope the above makes sense, otherwise please ask and I will try to provide more details.

    Any help you can provide will be very appreciated.

    I hope I put this in the right forum, because I realize there's also one for Pivot Tables, but as this is a question about a formula I thought this one was more appropriate.

    /Bendy

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi Bendy, and welcome!

    I am going to move this to the PivotTable forum, just because GetPivotData is so closely related.

    I'm curious if you might be able to upload a stripped down workbook with no confidential information in it, just so we can see how you laid out your data... I think I have a solution for you, but to get it right would involve seeing the exact headers for rows and columns.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken,

    Thanks for your quick reply. I've made a quick and simplified version of my file and attached it here.

    A quick description of the file:

    Report contains the sales by current month and year to date, using GETPIVOTDATA formulas. I've included both the formula with fixed values that works, and the one with cell references (via VLOOKUP) that doesn't work.

    Data contains the raw data.

    DataPivot contains the pivot table.

    Parameters contains the parameters used for the period selection via VLOOKUP.

    I've highlighted the cells that are causing problems in red (both the formulas and the referenced parameters).

    Please let me know if you have any questions.



    Another issue by the way: Even when using fixed values I can only use either multiple data_fields or multiple fields, so either multiple months or multiple countries. That's why in the YTD formulas i've used two GETPIVOTDATA, one for each country. If I use both in the same formula it returns an error.

    Do you know if it's possible to make multiple selections for both in the same formula, or do I need to use multiple formulas?



    Thank you very much for your help.
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    How many products/countries are you going to end up with overall? Lots of each?

    To be honest, my first temptation here would be to create a second Pivot Table that summarizes by product type, then country, add totals and pull it out that way. (Having said that, one of the Pivot Table gurus will probably give me a little grief for suggesting is, as I'm sure there is a more efficient way.)

    The issue I'm seeing here is that you have the product showing up twice in the table (so far), and it could be many more times...

    For summing multiple columns we can use an Index Match combination. (The one in the file attached looks pretty ugly, but you could shorten it with some named ranges, and by breaking the Match components out to helper cells.)

    Example attached.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Thanks for your suggestion. The formula would need to be updated though to check if the start and end months are the same, otherwise in January I get double values. An IF sentence should solve this though.

    By the way there are 3 countries and around 100 products, so there's more data in the full version.

    If you come up with the solution to selecting multiple data_fields with GETPIVOTDATA via cell references I'm still very interested as I prefer to maintain just one PIVOT table and I like the flexibility of the GETPIVOTDATA method.

    Thank you very much for all your help so far.

  6. #6
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    Your problem is that your data is not in the proper normalised form for generating Pivot Tables, and you are making life difficult for yourself.

    Your data should have just 5 headings, and not grow as you add more periods.
    Country, Area, Product, Period, Value

    The attached worksheet has a macro that will convert your data into a normalised form, as below
    Code:
    
    Sub NormaliseData()
        Dim wsS As Worksheet, wsD As Worksheet
        Dim lr As Long, lc As Long, i As Long, j As Long, k As Long
    
        Set wsS = Sheets("Data")
        Set wsD = Sheets("Data2")
    
        lr = wsS.Cells(Rows.Count, 1).End(xlUp).Row ' number of rows of data
        lc = wsS.Cells(1, Columns.Count).End(xlToLeft).Column ' last column populated
        
        wsD.Range("A1") = "Country"  ' fill headings on new sheet
        wsD.Range("B1") = "Area"
        wsD.Range("C1") = "Product"
        wsD.Range("D1") = "Period"
        wsD.Range("E1") = "Value"
        
        k = 2  ' first row of data to be written on new sheet
        
        For i = 2 To lr ' start from row 2 of source data to the end
            For j = 4 To lc  ' first three columns are standard, but loop for each column
                                   ' from column column 4 to last column
                wsD.Cells(k, 1) = wsS.Cells(i, 1)
                wsD.Cells(k, 2) = wsS.Cells(i, 2)
                wsD.Cells(k, 3) = wsS.Cells(i, 3)
                wsD.Cells(k, 4) = wsS.Cells(1, j)
                wsD.Cells(k, 5) = wsS.Cells(i, j)
                k = k + 1
            Next j
        Next i
    
    End Sub
    Now with this as the Source data, you can create a Pivot Table which will show Grand Total by Row, as well as by column, so there is no need to sum ranges of values with GetPivotData.

    I think it is also easier to do your Vlookup once, rather than in each formula so I have created a second report area on your sheet Report. The month selected via dropdown is in B12, and in B11 I have the single Vlookup formula to give the column heading from the PT.

    Your formulae then simplify to
    Code:
    for each Product for the Month
    =GETPIVOTDATA("Value",Pivot2!$A$3,"Product",$A15,"Period",$B$11)
    
    for the Total for the month
    =GETPIVOTDATA("Value",Pivot2!$A$3,"Period",$B$11)
    
    for YTD for each Product
    =GETPIVOTDATA("Value",Pivot2!$A$3,"Product",$A15)
    
    for YTD Total
    =GETPIVOTDATA("Value",Pivot2!$A$3)
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  7. #7
    Hi Roger,

    Thanks for the solution you provided. Unfortunately my original data sheet is not only used for this pivot, but another pivot / dashboard as well. Rearranging the data could be done with your macro, but redoing the other pivot / dashboard would be a longer task though. I have not created the dashboard so it would take quite some time to understand it fully and redo it.

    Also for other purposes it's convenient that the data is organized like this, as it's easier to read the table if I want to check something directly in it.

  8. #8
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    Well if that is the case, don't use GetPivotData for your YTD figures.
    In column O of your Data Pivot sheet (assuming you are going to have 12 months of data) use
    =SUM(C2:N2) and copy down.

    I moved your second pivot so it was underneath the first. In reality, I would put them each on separate separate sheets to avoid the possibility of overlap as the data grows.
    My second Table therefore started in row 20 and the formulae for the YTD figures become

    Code:
    =INDEX(DataPivot!$O$21:$O$28,MATCH(Report!A15&" Total",DataPivot!$A$21:$A$28,0))
    
    copied Down
    
    for the Total, either
    =INDEX(DataPivot!$O$21:$O$28,MATCH("Grand Total",DataPivot!$A$21:$A$28,0))
    
    or simply
    =sum(C15:C16)
    See attached revised file
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  9. #9
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    If you are likely to have data beyond the month for which you select as current month, then use the formulae as shown in the attached file.
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  10. #10
    Hi Roger,

    Sorry for the late reply, but I haven't had much time to check here or to progress with my Excel issue. I will try to have another look soon and see if can use your last suggestions.

    Thanks a lot for your help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •