Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Karl_Hart
Creator
Creator

Consolidating Months into Quarters

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

M1Apr
M2AprMay
M3AprMayJun
M4Q1Jul
M5Q1JulAug
M6Q1JulAugSep
M7Q1Q2Oct
M8Q1Q2OctNov
M9Q1Q2OctNovDec
M10Q1Q2Q3Jan
M11Q1Q2Q3JanFeb
M12Q1Q2Q3JanFebMar

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

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution could be a calculated dimension like:

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

QlikCommunity_Thread_186573_Pic1.JPG

QlikCommunity_Thread_186573_Pic2.JPG

QlikCommunity_Thread_186573_Pic4.JPG

QlikCommunity_Thread_186573_Pic5.JPG

QlikCommunity_Thread_186573_Pic6.JPG

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

hope this helps

regards

Marco

View solution in original post

6 Replies
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.


talk is cheap, supply exceeds demand
MarcoWedel

Hi,

one solution could be a calculated dimension like:

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

QlikCommunity_Thread_186573_Pic1.JPG

QlikCommunity_Thread_186573_Pic2.JPG

QlikCommunity_Thread_186573_Pic4.JPG

QlikCommunity_Thread_186573_Pic5.JPG

QlikCommunity_Thread_186573_Pic6.JPG

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

hope this helps

regards

Marco

Karl_Hart
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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