Excel 2010 Pivot Chart - Group Each Month By Years

[PWD]

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€$

 

[PWD]

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:

=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€$

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

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
[tt]
=RIGHT(Z2,2)="17"
[/tt]
...and Assign the appropriate FORMAT.

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[PWD]

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

"... 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€$

 

[SkipVought]

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...
[tt]
I4: =SUMPRODUCT(--(MONTH(Table1[FirstAppt])=$H4)*(Table1[FinYear]=I$3))
[/tt]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

...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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

...also I'd use a formula in J3 & K3:
[tt]
J3: =LEFT(I3,4)+1&"-"&RIGHT(LEFT(I3,4)+2,2)
[/tt]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Never heard from you. Did this help?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[PWD]

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! . I've attached the,slightly, revised workbook.

Many thanks,
D€$

 

[SkipVought]

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.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein