Pivot chart overlay multiple horizontal axes

Aaaaannnnnnndddddyyyyyyy...

Ahem, I mean, "hi all,"

Is it possible to split the series of a pivot chart so they can be shown as overlays on the same criteria sets? Effectively creating a new horizontal axis for each series.

I've attached an 'beautiful' drawing which explains what I'm trying to achieve.

The route I have been wrestling with is to create a calculated field based on adding an arbitrary offset to one series over another based on the name of the series - but I'm struggling to make it work. I think, if I did make it work, I would then have serious problems changing my pivot field settings to % of row, % of column etc. - but I can't be sure!

CC

Hi Andy,

Thanks for replying.

In order:
Unk, yeh, I know

That's right (and thanks!) Yes it is a time series. Change the x axis to category? Like swap the time series and category 'order'? (which interleaves series 1 & 2) I think I misunderstand you here.

Yes, this is my typical response but I need to retain the flexibility of the pivot table and I'd need macros to make sufficiently flexible pivot table mirrors - I'm looking for a more general solution.



I'm able to get calculated fields along the lines of =if(<data item>=x,y,z) to work, but have been completely unable to get =if(rowfield(x)="string",y,z) to work. Is this possible?


CC

Aha!

I have added a calculated Item to the series field which adds an offset to one of the other items. (i.e. calcitem=item(x)+100)

I then move the field from rows to columns, then choose not to display the original item.

The only thing I'd like to add would be something along the lines of calcitem=item(x)+(1.1*(max(item(y)) but the max only operates on one item at a time, so the offset is not constant.

I feel like a computer running out of RAM.

Rather than use up attachment limit space, please see revised attachment above.

What if the row field has two identical lines apart from a differentiator which is not used in the pivot. Changing the adjusted count formula to account for the potential variations would be impossible (surely?!).

Creating a calculated item in the year pivot field has the same problem.