+ Reply to Thread
Results 1 to 22 of 22

Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

  1. #1
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Hello,

    I'm not sure if this is possible. I want to calculate data using pivot table data, but not within the pivot itself.
    This example data works giving me the totals, but if I would filter pivot data by AR Coordinator, data doesn't change. Is it possible to write a formula that will work and refresh when pivot data increase/decrease or when filtered?
    Formula syntax.....=(GETPIVOTDATA(" Amount",F3304:I3304)-SUM(D3304:E3304)); this is first line of data for >30 days but looks at the current Grand Total row (3304).


    > 30 Days $18,711,032
    > 60 Days $10,493,640
    > 90 Days $6,637,459

    I don't know how to send pivot data without confidential info, but I have attached image of field names; obviously bottom row is Grand Total.
    Attached Images Attached Images

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    change source (desensitize), create PivotTable again and attach excel file
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Ok, I've uploaded the file with the pivot table.

    TIA,

    Regards,
    Jan

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Quote Originally Posted by janljan View Post
    Ok, I've uploaded the file with the pivot table.
    you didn't

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    To attach an Excel file to your post,
    • desensitize data
    • remeber that your example should reflect structure and type of data
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  6. #6
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    I can't find my attachments so uploading again; maybe file was too large.

    Regards,
    Jan

  7. #7
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Is it attached now?

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    xlsx - 0.97 MB
    xlsm - 0.97 MB
    xlsb - 9.97 MB

    no, not attached

  9. #9
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    I think file size was my issue for attached. I've tried it again.

    Thank you for your patience.

    Regards,
    Jan
    Attached Files Attached Files

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    You are welcome

  11. #11
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,375

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    I think you are having the problems with formula's in I1 thru I3

    so I changed them..

    it takes a little creative thinking with the getpivotdata formula and an iferror to handle if the column does not exist for that filtering.

    the idea is
    for the first row >31 days you need grandtotal except column 1-30
    for the second row >60 days you need above total minus column 31-60
    for the third row >90 days you need row2 total minus colum 61-90

    see attachment for the exact formulas
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Hi Roel,

    No that isn't correct. First $ amount is from your formula ( I had to adjust for >30 as it should have been 31 -60 (my error). Second $ amount is what it should be.
    Also when data is filtered for AR person, the number to not adjust correctly.

    > 30 Days $5,620,647 $31,911
    > 60 Days $5,618,922 $30,186
    > 90 Days $5,612,976 $24,240

    Kind regards,
    Jan

  13. #13
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,375

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    You have to make more clear what expected outcomes are under which conditions cause what you are showing is not making any sense to me

    you are writing a bunch of numbers but i do not know how to find them in your sheet

  14. #14
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Hello Roel,

    My numbers are coming from adding the grand total row amounts. Example:
    > 30 is the total sum of 31-60 + 61-90 + 91-180 + Over 180 which is $31,911.
    > 60 is the total sum of 61-90 + 91-180 + Over 180, which is $30,186.
    > 90 is the total sum of 91-180 + Over 180, which is $24,240.
    I want these total to refresh as data changes in the pivot or when a filter is set.

    I hope this better explains the result I want.

    Thank you for your help.

    Kind regards,
    Jan

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    I think three array formulas would suffice
    =SUM(IFERROR(GETPIVOTDATA("Amount",$A$5,"Age / Net Arrears",{"31 - 60","61-90","91 - 180","Over 180"}),0))
    =SUM(IFERROR(GETPIVOTDATA("Amount",$A$5,"Age / Net Arrears",{"61-90","91 - 180","Over 180"}),0))
    =SUM(IFERROR(GETPIVOTDATA("Amount",$A$5,"Age / Net Arrears",{"61-90","91 - 180","Over 180"}),0))
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  16. #16
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,375

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    \@janljan that was clear as water

    xlnitwit gave you a solution with array formula's.. which seem to work fine..

    I also worked on a solution. (I tend to avoid array formula's as I often struggle with them )
    On top of the (working) formula's I had some suggestions for formatting your pivot table. mainly
    - to put the AR Coordinator field as first column, which gives you the opportunity to collapse the long pivottable to only show totals per coordinator making it easier to compare them in one view. While you can still drilldown / expand to individual customers and/or invoices.
    > for this overview to work as I present it in the attachement, you also have to change the fieldsetting of the AR coordinator field on the layout& print tab to Show item labels in outline and check
    "Display subtotals at top" and ofcourse activate subtotals per AR coordinator.

    - Also I suggest using slicers on posting key, Dispute status an AR coordinator because they are visable clues to let you see right away which filters have been applied.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Hi Don,

    That is close; works when the data is not filtered. Doesn't work properly when pivot is filtered data.
    Select AR coordinator Lieu. There is grand total data for "not due" and under 91-180, yet all three items (> 30, >60 >90) show $8514.

    Kind regards,
    Jan

  18. #18
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Hi Roel,

    I'll take a look at your version possible later tonight/ tomorrow. Please note that for desensitizing purposes I removed customer names on the data. The pivot is driven by the customer name for purposes of aging, AR Clerk is secondary.

    Kind regards,
    Jan

  19. #19
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,375

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Hi janljan,

    DO what it what you like.. I understand about the customer names I saw that from your first screenshot. Also I used to work as Manager of a Debt collection department of a large company.
    So I understand the overview well (with customer focus) But I also had an overview per AR coordinator too because it gave me insight to their workload a lot of >90 day invoices meaned that they had to prepare a debt collection case and for larger amounts they needed additional support from legal.. So for me this extra view had added value to plan work and see how agents were doing with their customerbase.. Maybe I should have mentioned it while posting my solution.
    The shift of focus to AR coordinator might be of no or secondairy importance for you. Use it if you like, forget about it if you do not see added value. That's fine, I just wanted to suggest it.

  20. #20
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Quote Originally Posted by janljan View Post
    Select AR coordinator Lieu. There is grand total data for "not due" and under 91-180, yet all three items (> 30, >60 >90) show $8514.
    What should the results be, and why?

  21. #21
    Registered User
    Join Date
    04-04-2017
    Location
    LEMONT
    MS-Off Ver
    Office 13
    Posts
    50

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    Hi Don,

    Lightbulb when on. I understand why the results are all the same when there may be no data for >30, >90.
    One other question. Now that the array formula works to give me the result needed, I would like to add another column next to each of the results to show the % of total for each (>30, >60, >90). I have attempted a few ways, but can't get it to work. I guess I don't understand how to make this work with pivot table data. Any thoughts?

    Kind regards,
    Jan

  22. #22
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Calculate pivot table data outsides of pivot that refreshed when pivot data is updated

    The % of which total?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot table issue, I'm not able to capture new data, when table is updated
    By johnnyq113 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-26-2015, 05:01 PM
  2. Formatting problem in Pivot table for printing when data get refreshed
    By paradise2sr in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-26-2014, 05:22 AM
  3. Replies: 0
    Last Post: 03-16-2011, 08:15 PM
  4. Replies: 2
    Last Post: 01-23-2010, 11:38 AM
  5. [SOLVED] Disappearing Data Items on Pivot Table -Updated Question
    By GeorgeChe in forum Excel General
    Replies: 4
    Last Post: 08-02-2005, 11:05 AM
  6. Replies: 1
    Last Post: 07-06-2005, 01:05 PM
  7. [SOLVED] How can deleted data reappear in a refreshed pivot table in Excel
    By excel_user123456 in forum Excel General
    Replies: 3
    Last Post: 02-23-2005, 05:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1