Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Chart Question (2003)

    I'm having a problem figuring out if this can be done.

    I have attached a sample worksheet and a resulting pivot table. From the pivot table I can easily create charts. I have been asked to add another line to the chart - the total of all agencies against one agency. In other words, where the Total line is, that should become another series on the pivot chart. I thought I could add another column in my data called All Agencies and just list the total values. But when I added that additional column to the page area of the pivot table, I lose the data from the Agency column. Any suggesions how I can accomplish this using a pivot chart?

    TIA.

    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Chart Question (2003)

    I would organize the data differently. See the attached version. It's not clear to me, however, how you want the chart to be organized.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Chart Question (2003)

    Thanks for looking at this Hans. I get this dataset from another user already set up in the fashion that I displayed but I'm sure I can twist her arm to change it around if it will help.

    Using your dataset, I have made a pivot chart using one of the agencies and one of the services that that agency offers. Now, the powers that be would like to see an additional line showing the total referrals of all agencies (i.e. agency A, B, C) plotted on the same chart as the agency on my mock chart.

    I have created another chart to show what it is they are after using the method of choosing only the lines from the data that I want. Because this is going to involve dozens of charts (every week!), I'm hoping I can use a pivot chart to just tick a couple of boxes and have the chart look like my "End Result" chart. Am I missing something?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Chart Question (2003)

    What you want is not really a pivot chart, so it'll be extra work, I think. You'll have to create a non-pivot chart first and then add data from the pivot table. See attached version. Because the chart is not a real pivot chart, it doesn't update itself correctly if you select another service.

    Perhaps someone else (Steve, where are you?) will come up with a really clever idea.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Chart Question (2003)

    My thanks again Hans.

    I'm glad to know that I wasn't missing something obvious with this one. I'll keep playing around with the data in the meantime.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Chart Question (2003)

    I am not completly cleat on what you want to plot and how you want to change it.

    As the X it seems you want the dates.

    Do you want to be able to select which Agency is plotted and then list all the services as 3 sep lines and in addition have the totals line?

    Do you want to be able to select which Service is plotted and then list all the Agencies as 3 sep lines and in addition have the totals line?

    Something else?

    Are the number of Agencies/services/ dates all variable (right now you have 3 of each) or do some have constant numbers of items?

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Chart Question (2003)

    Thanks for jumping in Steve.

    Yes, I want the dates to show on the X line.

    I'll try and explain further. We have 29 agencies; all of which provide a variety of up to 20 different services. Each week, I am given a spreadsheet that shows how many referrals we gave to each agency for each service they are contracted for. I currently have 50 weeks of data and will soon be hitting the end mark of 52 weeks at which time I will start this nonsense all over again!

    For example, company "A" is contracted for nursing, homemaking and wound care. Company "B" is contracted for nursing, wound care and physiotherapy etc. I have been showing via pivot charts, the number of referrals given to each agency each week by service . So I was potentially providing a number of charts for one agency as they provided many services ie. one chart shows the number of referrals we gave Agency "A" for nursing; another chart shows the number of referrals we gave Agency "A" for homemaking and on it goes through each agency for each service they provide.

    Now the "top dogs" want to see the following: a chart showing the number of referrals per week by agency for one service (which I am already providing for them via pivot charts) compared to the TOTAL number of referrals for the service. So, if we gave out a grand total of 50 referrals for nursing in one week to three contracted agencies, they want to see that Grand Total plotted against Agency "A"'s referrals for the week.

    Does this make sense? I can email you a scrubbed version of my actual workbook, but my previous examples depict pretty much what I'm describing. In my perfect world, I would end up with a pivot chart that allows me to select one agency, one service, and also include the total referrals we gave out to everyone for that particular service on a separate line.

    Thanks for any insight you can throw on this one. (Phew...I've edited this about 10 times for clarity; I hope my efforts pay off <img src=/S/smile.gif border=0 alt=smile width=15 height=15> )

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Chart Question (2003)

    Here is a different approach. It uses SUMPRODUCT formulas instead of a pivot table to get the data needed for the chart.
    Cells G1 and G2 contain dropdown lists (using Data | Validation) to select a service and an agency.
    The chart is updated automatically when you select a different service or agency.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Chart Question (2003)

    Is this what you are after?

    This is the first time I have played with a pivot chart (it was not available in XL97). It seems that adding new ranges is not allowed. I worked around this by adding 3 new rows to the dataset itself (the number of new rows will be the same as the periods).

    The first column of each will read the the value of the page field in the pivot table (A20:A22) all have "=$g$2"

    The second column (B20:B22) has the text "Total" (modify the text as desired)
    The third has array formulas (confirm with contrl-shift-enter) to calculate the total, based on the page field:
    =SUM(IF(($C$2:$C$19=C20)*(($A$2:$A$19=$G$2)+($G$2= "(All)")),$D$2:$D$19))

    You will have to modify the range based on your data.

    So in essence all you need is crate the pivottable with your data, add the additional rows at the end, create the formulas, then modify the range of the pivot table to include the additional rows. there will not be a "totals" field in addition to all the other "Agencies".

    If Agencies is the page field and Services is the column field just modify the formula as needed.

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Chart Question (2003)

    Wow! Amazing!

    Thanks to both Steve and Hans...both examples provide the solution that I need. I'll figure out which one is easiest to replicate with my data and use it but both are outstanding examples of creativity.

    The help I've received is very much appreciated. Have a wonderful day!

    Deb

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pivot Chart Question (2003)

    FWIW, I think you might be able to do this with a pivot chart, but it's a bit fiddly. You would need to create a calculated item within the Agency field that is simply =A+B+C+...etc. This should then plot as the total for all agencies for a given Service/Date combination. You could probably also automate the creation of the calculated item to save a bit of time.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Chart Question (2003)

    Thanks to all for the input. It definately started the creative thinking going. I finally got the information to work the way I wanted via a pivot chart...it's one of those things that came to me while brushing my teeth!

    Anyway, for anyone else looking to do something similar, I've attached my end result.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Chart Question (2003)

    Thanks for sharing your solution.

Posting Permissions

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