+ Reply to Thread
Results 1 to 11 of 11

Pivot table - see the filters that calculated cell contents

  1. #1
    Registered User
    Join Date
    02-01-2011
    Location
    London, England
    MS-Off Ver
    Office 2007 Standard
    Posts
    11

    Pivot table - see the filters that calculated cell contents

    Hello

    I'm creating some quite complex pivot tables that will be used in Compact view by people without much pivot table experience.

    I would be useful if they could easily see the calculations that the data in each cell derives from.

    E.g. On one there are two row labels, the first filtered to all but blanks, the second to two specific values. The column labels are further filtered to remove blanks, and the values field is a calculated field pulling totals from several source columns.

    But I'm not sure it's that obvious to someone with only a bit of pivot table knowledge!

    Is there any quick and easy way for users to say hover over a cell and see something like:

    Sum of (widgets AND thingies AND gizmos) where salesmanager=(sid OR jane) and customer=(whatever Row Label value we're over)
    I'm not talking about the underlying source data, just the calculations.

    Thanks

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Pivot table - see the filters that calculated cell contents

    You can rewrite the labels in the row fields. Does that help?
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Pivot table - see the filters that calculated cell contents

    If you modify the name of your calculated field to be whatever you want to appear would that help ?

    So assume you name Calculated Field: "widgets AND thingies AND gizmos"

    When you hover over a data point in the Pivot the Contextual Tooltip should appear along the lines of:

    "Sum of widgets AND thingies AND gizmos"
    Value = n
    Row: customerlabel
    Column: columnlabel

  4. #4
    Registered User
    Join Date
    02-01-2011
    Location
    London, England
    MS-Off Ver
    Office 2007 Standard
    Posts
    11

    Re: Pivot table - see the filters that calculated cell contents

    Thanks both - good advice, and yes, I've named the calculated field and the row labels.

    But I'd like users to be able to see past my name for the calculated field so they can validate that they understand and agree that it's the correct data being calculated.

    For the row labels, having a fixed label is a start, but if someone else changes the filter and saves then it might make things even more confusing. Even for experienced pivot table users it'd be helpful to have immediate feedback on what filters are in force - for instance if there are 3 row fields it's a pain to have to check each one from the dropdown and easy to miss all the ticks

    Forgot to say: using Excel 2007 by the way.

    Thanks

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Pivot table - see the filters that calculated cell contents

    I'm afraid at present I can't fathom why the Contextual Tooltip would not suffice ?
    Attached is a very basic example - hovering over H5 would display the tooltip detailing the intersecting labels etc

    I've little doubt I'm misinterpreting requirements!

    CC might have the answer for you but in the meantime perhaps worth providing a simplified example to illustrate requirements ?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-01-2011
    Location
    London, England
    MS-Off Ver
    Office 2007 Standard
    Posts
    11

    Re: Pivot table - see the filters that calculated cell contents

    Thanks DonkeyOte

    I've added a second table and attached it below to give you an example.

    Hover your mouse over j16.

    There are 5 rows with an 'a' and a 'p' but only one is included in the calculation. As far as I know the only way to identify why is to:
    1. Go to the row labels filter
    2. Check the filter applied to f1
    3. Choose the dropdown next to f1
    4. Check the filter applied to f2
    5. Choose the dropdown next to f2
    6. Check the filter applied to f3
    7. Cancel out of the filter

    If you aren't used to using pivot tables a fair bit, that's by no means obvious.

    And even if you are used to using pivot tables, I challenge you to do that and remember what the criteria in f2 was after you close the filter panel

    Obviously with p's and q's and whatever it's a bit artificial, but this kind of structure is pretty typical of the sorts of tables I'd be using.

    Does that clarify?

    Thanks
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Pivot table - see the filters that calculated cell contents

    Quote Originally Posted by cookie365
    Does that clarify?
    It does yes - I guess the issue comes down to viewing in compact form and whether or not expanding the field list would be sufficient in terms of verifying the breakdown.

    I'm by no means a Pivot expert and there are others here who are far more knowledgeable but I suspect the only practical (vaguely) way of doing this would be to add comments to the databodyrange each time the PT updates (deactivating tooltips)

    However, this would raise issues re: legacy comments etc and PT resizing etc...
    you could cache the DataBodyRange address with each update (end of routine) - such that you can purge the prior bodyrange of comments before renewing (beginning of routine)

    The other alternatives as I see it would be to

    a) expand when checking (though this would still only show intersecting labels)

    b) create header strings outside of the PT itself that detail row label selections (generated via PT update event)

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,806

    Re: Pivot table - see the filters that calculated cell contents

    Hi cookie365,

    I'm late in this conversation but after reading the last few posts, are you looking for the SLICER tool for Pivot Tables, now included in 2010? http://www.databison.com/index.php/s...emote-control/
    It stays viewable and shows what is being used as the Pivot Table Filter. I believe you need to have 2010 Excel to get this ability.

    BTW - I needed the Slicer before 2010 came out and created a very non-standard way of showing what was being used as a filter. It took a lot of code and effort with validation dropdowns, etc...

    I hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Pivot table - see the filters that calculated cell contents

    @cookie365, attached is a rough outline of my prior post in action ... alterations to the Pivots should result in updated comments etc

    obviously some fairly major caveats:

    -- this is by no means intended as a finished article and is designed for XL2007+

    -- I suspect you would need to account for shared caches etc...

    -- the code has a hint of the sledgehammer about it but I confess that I was struggling to come up with anything remotely elegant

    -- there is hidden sheet in the file which acts as a cache of Pivot table names & ranges etc...

    edit: attachment reloaded - slightly more robust version
    Attached Files Attached Files
    Last edited by DonkeyOte; 02-04-2011 at 07:23 PM.

  10. #10
    Registered User
    Join Date
    02-01-2011
    Location
    London, England
    MS-Off Ver
    Office 2007 Standard
    Posts
    11

    Re: Pivot table - see the filters that calculated cell contents

    Thanks all

    MarvinP: yes, that's the sort of thing I'm after. Now I just need a bit of data that somehow fools my boss into paying for us all to upgrade to Office 2010

    DonkeyOte - thanks for the code - haven't had a chance to look at it yet but I promise some feedback as soon as I can.


  11. #11
    Registered User
    Join Date
    02-01-2011
    Location
    London, England
    MS-Off Ver
    Office 2007 Standard
    Posts
    11

    Re: Pivot table - see the filters that calculated cell contents

    Update - had a look at the code - absolutely what I had in mind and behaves just the way I'd expect Excel to work out of the box.

    I'm not too strong on VBA but I'll definitely spend some time working through it to see if I can incorporate it into my production workbooks.

    Thank you so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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