×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel 2010 Pivot Chart - Group Each Month By Years

Excel 2010 Pivot Chart - Group Each Month By Years

Excel 2010 Pivot Chart - Group Each Month By Years

(OP)
Good afternoon, I've done "some" searching and not found a solution. What I would like to display is April of years 1, 2 & 3 etc. together followed by May of years 1, 2 & 3 etc. so that my bosses can have a look and see how each month's demand compares with previous years.

Is this possible?

At present I have Grouped by Month & Year but this just displays each value chronologically. I wondered if there was some kind of 'SORT' that could be applied, or anything I could do with what I've got. The data from previous years won't be changing, so if it would involve manual reordering then, I guess, it would be too onerous.

Oh yes, there's one more thing, they'd like it grouped April to March of each year! I guess I'm going to have to manipulate the data some prior to pivoting it?

Many thanks,
D€$

RE: Excel 2010 Pivot Chart - Group Each Month By Years

(OP)
OK, I Think I've got some sort of solution. I added another 'helper' column to the data table and used that to calculate the Financial Year:

Quote:


=IF(ISBLANK([@FirstAppt]),"", IF(OR(YEAR([@FirstAppt])=2017, AND(YEAR([@FirstAppt])=2018,MONTH([@FirstAppt])<4)),"2017-18",
IF(OR(
AND(YEAR([@FirstAppt])=2018,MONTH([@FirstAppt])>3),
AND(YEAR([@FirstAppt])=2019,MONTH([@FirstAppt])<4)),"2018-19",
IF(OR(
AND(YEAR([@FirstAppt])=2019,MONTH([@FirstAppt])>3),
AND(YEAR([@FirstAppt])=2020,MONTH([@FirstAppt])<4)),"2019-20",
"2020-2021"))))
And named the Column FinYear
(Any better way of doing this would be gratefully received BTW.)

I then refreshed the pivot to include this column.
I then grouped the Pivot Table by Months.
I then added FinYear to the Axis Fields.

This now groups each month together, and within each grouping are the particular years.

All I need to do next is find a way to change the colour of each Financial Year!!

Thanks for listening.

Many thanks,
D€$

RE: Excel 2010 Pivot Chart - Group Each Month By Years

Quote:

What I would like to display is April of years 1, 2 & 3 etc. together followed by May of years 1, 2 & 3 etc.
In a helper column...
=TEXT(FirstAppt,"mm-yy")

...will sort

01-17
01-18
01-19
02-17
...

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2010 Pivot Chart - Group Each Month By Years

Quote:

All I need to do next is find a way to change the colour of each Financial Year!!

Select the range to be shaded
Open the Conditional Formatting Wizard
Assuming that the TOP-LEFT cell in the Selection is Z2, use the following formula

=RIGHT(Z2,2)="17"

...and Assign the appropriate FORMAT.

Use the same formula changing "17" to appropriate year and FORMAT accordingly for remaining years.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2010 Pivot Chart - Group Each Month By Years

(OP)
Thanks Skip. I have attached an abbreviated copy of the workbook to show exactly what I've got.

I think my brain had almost overheated yesterday but what I'd meant to say was

Quote:


"... find a way to change the colour of each Financial Year's column in the chart."

All I was able to do was change the colour of each individual column, well, individually!

Many thanks,
D€$

RE: Excel 2010 Pivot Chart - Group Each Month By Years

Just had a few minutes to gen this. Hope it's what you need.

Here's the formula. Probably could have been done via PivotTable/Chart, but I prefer Excel Chart...

I4: =SUMPRODUCT(--(MONTH(Table1[FirstAppt])=$H4)*(Table1[FinYear]=I$3))


Also, seems that series 1 should be assigned to the X-Axis.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2010 Pivot Chart - Group Each Month By Years

...also, regarding your formula to calculate Fiscal Year (FY):

I'd put that data in a table and then use a lookup in my formula using...

=INDEX(FY,MATCH(LookupValue,LookupRange,1),1)

In this way, as time progresses, you need only add to or modify the table, rather than modify a formula that can get unwieldy and indecipherable.

Avoid putting variable data in formulas and code!

FYI, in my experience at 4 major aircraft manufacturers, values like fiscal year, accounting month, manufacturing day, calendar day were all determined corporately and stored in a manufacturing calendar table that was available to all and used by IT in all date calculations. In a calendar, there is no ambiguity.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2010 Pivot Chart - Group Each Month By Years

...also I'd use a formula in J3 & K3:

J3: =LEFT(I3,4)+1&"-"&RIGHT(LEFT(I3,4)+2,2)


That way only the Staring Fiscal Year need be entered as time passes.

I'm out and about on my iPad which has fewer Excel features than my Laptop. Otherwise I'd look at the chart and the possibilities of a PivotTable/Chart.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2010 Pivot Chart - Group Each Month By Years

Never heard from you. Did this help?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2010 Pivot Chart - Group Each Month By Years

(OP)
Hi Skip, apologies for the lack of response. I must confess that I have confused myself looking at your responses.

BUT, on the positive side, what I would like to explore is the idea of putting the Financial/Fiscal Year parameters in a table and, somehow access that. I've amended the formula in Cell H4, as a test, but I may have missed the point as I can't see any great advantage in doing it that way.

The other issue was to do with formatting the individual columns of the Pivot Chart - I still don't know how to colour the bars for each year other than doing each column, one at a time! I've just updated the data - adding the October figures - and it appears to have coloured the October 2019-20 the same as the rest of 2019-20, which is nice! bigsmile. I've attached the,slightly, revised workbook.

Many thanks,
D€$

RE: Excel 2010 Pivot Chart - Group Each Month By Years

Quote:

I still don't know how to colour the bars for each year other than doing each column, one at a time!

Just drag the FinYear field from ROW to COLUMN.

Quote:

what I would like to explore is the idea of putting the Financial/Fiscal Year parameters in a table and, somehow access that

Start another thread and post your SQL for qry_Seen_Months_2019_10_08.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close