It would depend on your data format exactly, but you could use something like reference lines / bands perhaps?
That was my idea but i wasn't able to get it to work.
Using reference lines to overlay calendar events will work better if you can create a parameter for each event. If you are only plotting Holidays this should be fairly easy to implement. (See instructions below/attached workbook.)
You can also add the events to your graph by using data blending with a dual axis in the graph. This may be more ideal if you are planning on overlaying a lot of promotional events/ if you are relying on the users to maintain this information. Using this approach, you can have the events dynamically populate based on the latest Excel data. You also will have more flexibility in how the events can be plotted. (See instructions below/attached workbook.)
- Create one parameter for each holiday and list the date as the value
- Right click the Date axis to add a reference line and set the value to one of your holiday parameters and the aggregation to Minimum. Set the label to Computation.
- Repeat for each holiday parameter
Using Data Blending/Dual Axis
- Add a number column [Num] to the Events data and enter the same number in every row
- Make sure that both of your date fields are named the same so that you can use data blending
- Add the new [Num] field to Rows on you graph and set the aggregation to Max
- Right click on the y-axis and select Dual Axis
- Drag Event to labels
- It looks best if the labels can be at the top of the graph, so you might need to edit the axis if they aren't at the top
- Right click secondary axis and uncheck Show Header
- Option to switch the graph type for [Num] to bar for pseudo reference lines or to line/circle to have an indicator above the graph lines
Thanks for your suggestion. The problem is that the events are coming from a flat file that is to be updated, so manually creating the events is not preferable. I did find a 'hack' by creating a time line and posting it above everything with a line for each event, however I can't get the end points (ie the dates at the beginning and end of the window) to match the rest of the graphs in workbook.
Use a dual axis chart. Make your regular viz the way you want it. Then blend in the flat file on date, and create some sort of measure on that date in the flat file.
Create a second sheet (just for testing purposes) that displays the flat file dates with a bar chart using SIZE to make the bars really skinny so that they look like lines. Once you have control of that, go back to the regulat viz from above.
Drag SUM([that measure]) onto rows, mark it as dual axis, and shape those marks the same as you did on the test sheet. You should be able to make a nice graph with lines marking the various holidays.
And now that I look back, that's one of the suggestions Sarah made. You can align the axis for the two dual axis parts using the synchronize axis feature.
Jon -- Are you using dual axis? If so, have you synchronized the axes? (Right click on the right-side axis. Choose edit axis. Select "Synchronize axis" from that edit box.)
OK, at this point I recommend that you post a packaged workbook. The number values you have along that bottom line seem to be the same numbers you have on the yellow line. Probably that's a result of hacking up a screen shot to show here...
I also recommend that you start a new thread. You'll get a bigger audience on a new thread. Right now the only reason I saw this because I got an email notification that a thread I previously posted in had an update.
Yeah definitely will do that. Thanks for the help.