0

Hi have this pivot table the pivot table is basically as follows

Project1 - 01/01/2010 | some data
Project2 - 01/06/2010 | some data
Project3 - 01/05/2010 | some data
Project4 - 01/04/2010 | some data
Project5 - 01/03/2010 | some data
Project6 - 01/02/2010 | some data

The project dash date is a concatenation of two column in the original dataset I want the pivot to sort by date

I can add the date to the pivot table so it is sorted by date. Like bellow.

01/01/2010 | Project1 - 01/01/2010 | some data
01/02/2010 | Project6 - 01/02/2010 | some data
01/03/2010 | Project5 - 01/03/2010 | some data
01/04/2010 | Project4 - 01/04/2010 | some data
01/05/2010 | Project3 - 01/05/2010 | some data
01/06/2010 | Project2 - 01/06/2010 | some data

but then my chart shows redundant data. Is there a way to keep the date in the pivot only for sorting purpose. but my chart does not need to show the extra date ??

I tried removing the concatenation, and just show the pivot as is. But I am still having problem because having two X axis only allowed one of the axis to be displayed vertically UNLESS I can make both X axis to be displayed vertically then I am satisfied

thanks for the input

Improve this question
  • you can get away from the concatenation by "Repeating All Label Items" in the Report Layout section of the pivot table design tab of the ribbon. That way you would add the date and project, and they would display like a normal table (each row containing each label) – guitarthrower Feb 23 '15 at 20:16
0

If I understand you correctly, you should be able to just remove the data series you don't want from your chart, after creating it. Not all the fields have to be displayed in a Pivot Chart, just leave out the ones you don't want (the date field used only to sort the table).

Improve this answer
  • 1
    if I remove date field from pivot chart. my sorting get all over the place again. – user2635566 Jan 21 '15 at 0:53
0

I would use the Power Pivot Add-In for this requirement. It has a Sort by Column feature to do exactly what you want.

http://blogs.technet.com/b/jessmeats/archive/2012/06/12/powerpivot-sort-by-column.aspx

Improve this answer
0

I decided to just went with simple solution . and sort it as text

BEFORE
Project1 - 01/01/2010 | some data
Project2 - 01/06/2010 | some data
Project3 - 01/05/2010 | some data
Project4 - 01/04/2010 | some data
Project5 - 01/03/2010 | some data
Project6 - 01/02/2010 | some data

AFTER
2010/01/01 - Project1 | some data
2010/02/01 - Project6 | some data
2010/03/01 - Project5 | some data
2010/04/01 - Project4 | some data
2010/05/01 - Project3 | some data
2010/06/01 - Project2 | some data

it works for my purpose.

thank you

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.