Adding a timeline (current week) to a pivot-graph

[Jeroenadegraaf]

Hello,

I have made a PivotGraph with week-numbers on the x-axis. I want to plot the current week in the graph with a vertical line. My approach was a combined graph with lines for my data and a semi transparent column on the current week.

This seemed to work, but as soon as I use the slicer to select data, the lines of the graph are replaced by columns.

Any suggestions to solve this?

Jeroenadegraaf

[#2]

Hi @Jeroenadegraaf 

I'm working a lot with combined charts so hopefully, I can help you solve the problem.

Attached file shows 2 possible solutions. 

Option 1: add a vertical line for the current week

Option 2: mark the column with the current week with a different colour

Please play around with the file by changing the values in the yellow marked cells.

If this works, you should be able to copy it into your file and change the data source of the chart to your data

[#3]

@PascalKTeam 

Thank you for your reply. My problem occurs when I'm using a PivotGraph based on a PivotTable.

Do you have a sollution for that as well?

Regards,

JeroenAdeGraaf

[#4]

Hi @Jeroenadegraaf 

Yes sure. In attached document, there's a pivot chart on the right which marks the current week (from cell J2) in a different colour. Just change value in J2, refresh the pivot table and you will see.

[#5]

@PascalKTeam 

Can you take a look at the attached file and the way it responces when the slicer is used?

Jeroen

[#6]

Hi @Jeroenadegraaf 

You have added some additional rows and columns without updating the formulas, this is why it didn't work. Now it works, see attached file

[#7]

@PascalKTeam 

But can you combine lines for the labels with a column for the current week? Or a vertical line for the current week? Because I have about 90 labels in my graph and columns don't give good visual result.

The 90 labels are also the reason I need the slicer.

Jeroen

[#8]

Sorry I don't know what you're asking for

[#9]

@Jeroenadegraaf 

Here another option with a small vertical line which shows the current week

[#10]

@PascalKTeam 

Okay.. You can see the graph in my last file. It contains three lines and a column for the current week.

But as soon as I change the graph with the slicer, the lines in the graph change to columns.

I'm looking for a method to make a PivotGraph with lines and a marker for the current week.

Jeroen de Graaf

[#11]

@PascalKTeam 

Thanks for al your effort. I think it's a bug in Excel.

Jeroen

[#12]

Thanks for the explanation, now I see the issue. I don't know a smart work around and also couldn't find a solutions online, sorry for that.
If I have more time later on I will try again