I would like to be able to create a time series line chart that has data appearing at different frequencies and that has shaded regions representing economic recessions. The typical approach for creating recession bars is described here.
The standard approach described in the link works fine when all the time series have the same set of X values and where the column chart that describes the shading has the same set of X values. However, I cannot figure out how to make this work when the lines have differing X values, and where the shading series may have different X values from any of the lines.
For example, suppose I have a monthly series with dates at the end of every month, and elsewhere on the worksheet I have a quarterly series with dates at the end of every quarter. Finally, I have a series that will be used to define the shading and that has an observation every day. It would be possible to overlay the lines on one chart either by using a XY scatter chart or by using a line chart and setting one of the series to be of type scatter; I much prefer the latter option because I want to be able to define the tick spacing in terms of months, years, etc, which I can do with a line chart but cannot do with a scatter chart. However, I don't know how to make the daily shading series appear correctly then.
One solution would be to combine all series into a single daily frequency block, where the monthly series only has one observation per month and the quarterly series only has one observation every three months. However, I would prefer to not force the user to re-organize their data before charting.
The standard approach described in the link works fine when all the time series have the same set of X values and where the column chart that describes the shading has the same set of X values. However, I cannot figure out how to make this work when the lines have differing X values, and where the shading series may have different X values from any of the lines.
For example, suppose I have a monthly series with dates at the end of every month, and elsewhere on the worksheet I have a quarterly series with dates at the end of every quarter. Finally, I have a series that will be used to define the shading and that has an observation every day. It would be possible to overlay the lines on one chart either by using a XY scatter chart or by using a line chart and setting one of the series to be of type scatter; I much prefer the latter option because I want to be able to define the tick spacing in terms of months, years, etc, which I can do with a line chart but cannot do with a scatter chart. However, I don't know how to make the daily shading series appear correctly then.
One solution would be to combine all series into a single daily frequency block, where the monthly series only has one observation per month and the quarterly series only has one observation every three months. However, I would prefer to not force the user to re-organize their data before charting.