Line chart with days of week (AXIS) and values (lines) distinguished by months

Hi Guys

I am trying to create a pivot line chart based on the following conditions:

- Please view my illustration. I assumed it would easier than explaining. This is what i am trying to achieve.


Please find the file enclosed within.Pivot line chart.xlsxPivot line chart.png

Hello,

I don't think this can be done with a pivot table. You cannot plot the lines with dates on the X axis, because May is AFTER April, but you don't want it to plot that way.

You need a table with just the day names and then copy and paste (or use formulas) to get the pivot data. You can't group the pivot data by day name either, because each day would appear only once and add up all values for all Mondays, say.

So, create a helper table and create the chart from that helper table.

2015-06-10_22-34-39.gif

cheers, teylyn

Very insightful.
how can I link this to live data coming from a pivot table? I need to be able to refresh the helper table without pasting the raw data. Another issue which has been addressed in the response above is that some months start at Tuesdays while others Wednesdays etc. How can i have a calendar type line graph much like the one shown below. Essentially what i am trying to do is use the first Tuesday in may corresponds with the first Tuesday in June when comparing the trend lines.

: 2015-06-10_22-34-39.gif

Your data in Sheet 1 does not make sense. Why is the month name in column E different from the actual date in column A? Is that on purpose or is that a mistake?

Please see the attached file for a formula solution to prepare the data for charting.

Since your data does not have more than one value per day, you don't really need a pivot table. I have worked with the source data on Sheet1 instead.

I have changed the month names in column E to match with the dates in column A. I have added a helper table for the chart, starting in column H.

Column headers in row 2 contain the start dates for each month. Cell I2 contains a hard-coded date, the cells to the right use a formula to increment by one month. If you want the chart to start with a different month, just change the start date in cell I2. Make sure you enter a date with the first of the month.

Column H has the weekday names. The formula that starts in cell I3 and is copied across and down uses the number calculated in row 1 to determine if the weekday in column H matches the first of the month, and if so, it starts filling in the data from column C.

Let me know how that works for you.

cheers, teylyn

Perfect!!, Final question. How can I ensure that the current month data does not show as zero in the line graph. I want to prevent a straight line moving across at the bottom for data which has yet to be completed due to it being in the current month. Rather it would be better if data just stopped at the most recent day for that month.

change the formula in cell I3 to

=IF(I$2+ROW()-(I$1+1)>=EDATE(I$2,1),NA(),IF(ROW()>I$1,IF(INDEX($C:$C,MATCH(I$2,$A:$A,0)+ROW()-(I$1+1))>0,INDEX($C:$C,MATCH(I$2,$A:$A,0)+ROW()-(I$1+1)),NA()),NA()))

copy across and down.

I used the workbook I attached above, pasted the formula from post 7 into cell I3 and copied down. The numbers did not change. I copied across. No number changed. Just the zeros in the last month got replaced with #N/A.