Dynamic line Chart
I'm working on a simple dashboard that will contain highlights of information for baseball teams. I've successfully figured out how to change the picture dynamically through named ranges and Picture Links.
Now what I'd like to do is include a line chart on the dashboard page showing the total data points (could be anything- sales, attendance, revenues, popularity rating) for each month. The trick is not all of the teams will have information for every month and/or may not stop in Jan. and end in Dec.
I'm wondering if there is a way to dynamically create the chart so that it picks the correct range on the x and y axes and shows the data points for those month/data point intersections.
Any body know how to do that if it's possible?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, guys. I thought I'd included the spreadsheet. Here it is. MLB-Dashboard-a.xlsx
Hello,
One option is to create a pivot chart in the dashboard. The data for the pivot chart is underneath the actual chart. Use the filter in the chart to select the team.
Or, you can hide the filter selection button in the chart and use the filter in the pivot table. Drag the chart into the desired position to hide/show the pivot table filter.
If you don't want to use a pivot table, you will need to create a summary table somewhere else in the sheet to base your chart on.
A chart (other than a pivot chart) will not automatically summarise your data. You need to provide that. The Pivot Chart is the fastest way to achieve that with just a few clicks.
See attached.
cheers, teylyn
MLB-Dashboard-a.xlsx
One option is to create a pivot chart in the dashboard. The data for the pivot chart is underneath the actual chart. Use the filter in the chart to select the team.
Or, you can hide the filter selection button in the chart and use the filter in the pivot table. Drag the chart into the desired position to hide/show the pivot table filter.
If you don't want to use a pivot table, you will need to create a summary table somewhere else in the sheet to base your chart on.
A chart (other than a pivot chart) will not automatically summarise your data. You need to provide that. The Pivot Chart is the fastest way to achieve that with just a few clicks.
See attached.
cheers, teylyn
MLB-Dashboard-a.xlsx
ASKER
Thanks, Teylyn. Is there a way to still allow the user to change the team, which will change the filter and the image that shows?
ASKER
I just had an idea, but am not sure if it's possible or how exactly to write it. What if I created a hidden table somewhere. The first value row of that table would use the large function on the whole Date column, but only include items where the item in the Team Column is, say, the Yankees. I think there is an array formula or something that could accomplish that.
Then after that, following the lead from Dave, I could use a combination of the Counta and Offset Functions to determine how far down the columns the graph should reference for it's data.
Does that make sense?
Then after that, following the lead from Dave, I could use a combination of the Counta and Offset Functions to determine how far down the columns the graph should reference for it's data.
Does that make sense?
BBlue, in the file I attached you can change the filter to another team and the icon will adjust. You can either use the in-chart button to do that, or use the pivot table filter.
The pivot table is really the easiest way to summarise your data. I'll have a look into another suggestion.
cheers, teylyn
The pivot table is really the easiest way to summarise your data. I'll have a look into another suggestion.
cheers, teylyn
One question for clarification: If a team has values for Jan, Feb and April, but not for March, what do you want to see on the X axis? Four months and the line will connect the Feb and April values? That would be best practice. In a line chart, you don't normally omit months just because they don't have data. You either drop the line to 0 or leave a gap for the missing point or connect the points before and after the missing point.
Any thoughts?
Any thoughts?
ASKER
But the pivot table is hidden (because it makes the dashboard cleaner). So the user would have to move the chart, first, then change the filter.
ASKER
I think I'm going to try to write a macro that creates an array of all of the items that have the same team as the one in the cell containing the team name. It then will use the Excel large function to return the nth largest value. I don't know if it'll work, but now I feel challenged. LOL.
See attached.
the chart is set to connect data points with lines. Leaving gaps is not easily achieved when the source data is calculated with a formula (it can be done, but it requires a more complex setup). If you want the line to drop to zero, you need to change the source formula to show 0 instead of N/A.
Let me know if that is closer to what you want to achieve.
cheers, teylyn
Copy-of-MLB-Dashboard-a.xlsx
the chart is set to connect data points with lines. Leaving gaps is not easily achieved when the source data is calculated with a formula (it can be done, but it requires a more complex setup). If you want the line to drop to zero, you need to change the source formula to show 0 instead of N/A.
Let me know if that is closer to what you want to achieve.
cheers, teylyn
Copy-of-MLB-Dashboard-a.xlsx
What exactly are you trying to plot? See attached with teylyn's pivot approach. PS - click on the chart to see the settings and you can change months, teams, etc...
Dave
MLB-Dashboard-a-r1.xlsx
Dave
MLB-Dashboard-a-r1.xlsx
BBLue, can you please answer my question above: If a team has values for Jan, Feb and April, but not for March, what do you want to see on the X axis?
Do you want to omit the March altogether and have just Jan, Feb, April on the chart? That would be very bad charting practice. If you show a chart with months on the X axis, the reader will expect the third month to be the month after the second month.
Please explain what you want to achieve.
Do you want to omit the March altogether and have just Jan, Feb, April on the chart? That would be very bad charting practice. If you show a chart with months on the X axis, the reader will expect the third month to be the month after the second month.
Please explain what you want to achieve.
ASKER
I think you're right. We probably should show the absent months either way, but dropping to 0. I hadn't thought about that actually. What you provide above (35266409) works, though. That's what I was looking for, except I guess for 0 instead of N/A's
ASKER
Thanks to you, too, Dave. But I don't have the most savvy of users, so I'd prefer to not have them click on the chart and have to make changes that way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both very much! Thanks tylyn for making several attempts at what I was trying to get across. We finally got there. Sorry for the confusion. Dave, thanks for your help, too. That link for dynamic charting areas (using countA and offset functions) was also something I can keep in my back pocket for the future. Have a wonderful day/evening both of you.
ASKER
One quick follow-up question, teylyn. What do the double-dashes mean in your sumproduct formulas:
i.e.
SUMPRODUCT(--($A$1:$A$64=J
i.e.
SUMPRODUCT(--($A$1:$A$64=J
No worries and you were very generous with the points - teylyn did all the work :)
When you get around to doing the dynamic range stuff, don't hesitate to post - ask a related question to this or the other links I gave you and I / others will be quick to respond.
Cheers,
Dave
When you get around to doing the dynamic range stuff, don't hesitate to post - ask a related question to this or the other links I gave you and I / others will be quick to respond.
Cheers,
Dave
BBlu, thanks for the grade.
The double dashes convert the True/False results inside the brackets to 1/0 numbers, which can then be used in calculations. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html#format
The double dashes convert the True/False results inside the brackets to 1/0 numbers, which can then be used in calculations. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html#format
this would be a lot easier to solve if you provided some clue about your data layout. Where is the source data, what are the possible variances in the ranges, how is the data arranged (i.e. in columns or rows), etc, etc.
With the information you have provided above, the correct answer is: yes, it's possible.
You can create a chart based on a dynamic range that will include only populated cells from a specific row/column in a given table and show the appropriate X axis labels.
If you need anything more specific than that, you'll have to provide the framework to work with.
cheers, teylyn