Charting (PivotChart?) help needed

[smithchuck]

Hi all,

Below this description is a table with the data I'm trying to graph in various ways. It's a project where each client goes through a number of neurofeedback sessions and provides a self-reported score on how they're currently feeling about a problem area (a "Treatment Goal" or TG). So for example, in the first two data rows, Client ID 101, in Session Num 1, reported scores on two TG's: Attention (Score of 8) and Sleep (Score of 6). He had the same pattern of data points through 4 sessions.

Client 102 had three TG's on his first session (session num 1): attention, anxiety, and rumination. But you'll see when we get to his 4th session, he has added an additional TG: Sleep. There may also be some gaps in the data series (e.g., maybe client 101 didn't report a score for one TG for a particular session).

What I'm having trouble doing is getting satisfactory line charts with more than one data series. At this point the data points are ALWAYS going to be from the "Score" field, and the X-Axis labels are ALWAYS going to be "Session Num." What I'd like to be able to do is easily create charts for multiple clients who have the same treatment goal, and charts for one client showing all of their treatment goals. So for example, a line chart showing a line for all clients who have a problem with "Sleep" (each client's scores for Sleep represents one data series). Or I'd like to be able to show all the scores for all treatment goals for one client (so I'd have one chart that shows everything that client 101 is working on, with one line per treatment goal).

I’ve briefly fiddled with Excel PivotTables & PivotCharts, but haven’t gotten very far with it. One immediate problem is that it always seems to want to “Sum” or “Count” or in some way calculate the Score numbers, and I don’t want that. I want to use them as data series in line charts, with no other calculation.

The data is stored in FileMaker Pro 11, which exports easily to Excel. FileMaker has a built-in charting feature and I can pretty easily get a line chart for one client and one treatment goal, but nothing with multiple data series.

Thanks in advance,

...Chuck

Session Num Client ID Score TG Description
1 101 8 Attention
1 101 6 Sleep
2 101 8 Attention
2 101 6 Sleep
3 101 6 Attention
3 101 4 Sleep
4 101 7 Attention
4 101 5 Sleep
1 102 10 Attention
1 102 7 Anxiety
1 102 10 Rumination
2 102 4 Attention
2 102 5 Anxiety
2 102 4 Rumination
3 102 9 Sleep
3 102 5 Anger
3 102 4 Attention
3 102 7 Rumination

---------- Post added at 06:51 PM ---------- Previous post was at 06:49 PM ----------

That table formatting didn't work out very well. There are 4 fields/columns: "Session Num", "Client ID", "Score", and "TG Description".

[MarvinP]

Hi SmithChuck and welcome to the forum,

See if 2010 Excel Pivot Charting does what you are looking for.
http://www.youtube.com/watch?v=Vkwv1fl9KO4

With newer version of Excel you get more features and it makes stuff easier.
http://www.youtube.com/watch?v=zgt7SdrYJqg

[smithchuck]

Thanks, Marvin. I'll check these out. I'm more familiar with Excel 2003 but I do have access to 2010 (now that my big computer is back from the shop!). I am also working with someone else and I'm not sure which version of Excel she is using. Do you happen to know if PivotTables and PivotCharts created in 2010 are backwards compatible with Excel 2003?

[MarvinP]

Hi,

I believe they rewrote/improved the charting tools in the newer versions. That means I have no idea if they are backward compatible.

[smithchuck]

Yeah, I knew they had made changes too, but I'm not that up to speed with PivotTables. For me they're one of those features that is on rare occasions very useful, so as a result I re-learn how to do them every time one of those rare occasions pops up. But I found by Googling "excel 2010 pivottable backwards compatibility" that there's plenty of information out there--enough to mean I should probably find out what version of Excel my co-worker on this project is using! Thanks for the tips.

[Andy Pope]

See attached, hopefully this will help.

Use the Page filter on each table to select individual Client or Treatment

[smithchuck]

Andy, at a quick glance, that looks fantastic! I'll be studying it more this weekend, I hope. Thank you so much!

[smithchuck]

Hi again Andy,

In order to learn this, I tried recreating the Pivot Tables & Charts you provided and I think I managed to duplicate them but for one thing: the automatically-updating chart titles. I happened to do these in Excel 2003. My questions: did you create your example files in a later version of Excel? Or, did you do something special to create the automatically-updating chart titles? (e.g., change client ID from 101 to 102 and the appropriate chart title now says "102").

Thanks again very much, this is EXACTLY what I'm looking for!

[Andy Pope]

I used this method to link the pt filter to the chart title.
http://www.andypope.info/tips/tip001.htm

[smithchuck]

Thanks again!

[Cutter]

@ smithchuck

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[smithchuck]

Thanks, Cutter, for taking care of my oversight. I took care of the stars.