Consolidating Months into Quarters

[Karl_Hart]

Hi all,

I've had a request and I'm at a loss as to how to go about it. I have a series of bar charts that show performance on a month by month basis. As we get into the financial year there are more and more bars and I need to condense them down. I've been asked if I can collapse three months into a quarter once it has passed but show the monthly performance for the most recent months.

I know I've not explained that at all well so hopefully the below chart explains what I want to see each month a bit better than I can

Month                     Chart Dimension

M1		Apr
M2		Apr	May
M3		Apr	May	Jun
M4		Q1	Jul
M5		Q1	Jul	Aug
M6		Q1	Jul	Aug	Sep
M7		Q1	Q2	Oct
M8		Q1	Q2	Oct	Nov
M9		Q1	Q2	Oct	Nov	Dec
M10		Q1	Q2	Q3	Jan
M11		Q1	Q2	Q3	Jan	Feb
M12		Q1	Q2	Q3	Jan	Feb	Mar

Hopefully that is a bit clearer, and hopefully you can let me know of how this can be done using dimensions and not by having a whole series of Expressions with conditional formatting on them (I think this would take too long for the numerous reports I have set up)

Many thanks

[Gysbert_Wassenaar]

The usual way to deal with this is to use a Drill Down group. That way you can easily drill down from the quarter to the month. That usually makes more sense then putting quarter and month values next to each other. The quarter total are a lot larger than month totals so side by side comparison is pointless anyway.

If you're only showing the number values in a table then the user can use a pivot table and collapse/expand the quarter and month dimensions as needed to get the view he/she wants.

[MarcoWedel · Accepted solution]

Hi,

one solution could be a calculated dimension like:

=If(Month<Ceil(Max(TOTAL Month),3)-2,Quarter, Month)

Although I agree that comparing month values with quarter values is not very intuitive.

hope this helps

regards

Marco

[Karl_Hart]

Thanks for the comments, these reports are to be printed out so the drill down isnt an option.

As for the comparisons, its comparing percentages so comparing this months performance against the performance for the last three months is a valid one.

The main reasoning behind it is that a committee meets every quarter to review performance and so at the start of Q2 they have already looked at Apr/May/Jun in detail so dont need those individual months, but want it there so they can compare Jul/Aug/Sep against Q1.

I hope that makes sense.

Thanks again

[Karl_Hart]

Marco,

Thanks for the solution, this works great. As for it not being very intiuitive, please see my reply to Gysbert, I'm sure I'm not thwe only one for who this would be useful!

Thanks,Karl

[sunny_talwar]

Very awesome MarcoWedel‌‌

[Karl_Hart]

MarcoWedel‌

I've tried to implement this fix but I'm coming across an usual issue. I think it may be related to the fact that my fiscal year starts in Apr?!

The solution above works perfectly when using the FiscalMonthNumeric (See the top two charts in the attached) whereas if I use the Month field, it works until I get to January, it then puts January's data in Q1, Feb in Q2 and March in Q3. (The bottom 2 graphs in the attached)

The only difference in the two is one dimension says

If(FiscalMonthNumeric<Ceil(Max(TOTAL FiscalMonthNumeric),3)-2,FiscalQuarter, Month)

and the other is

If(FiscalMonthNumeric<Ceil(Max(TOTAL FiscalMonthNumeric),3)-2,FiscalQuarter, FiscalMonthNumeric)

Does anyone have an idea how to fix this?

Thanks