# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Contributor

## 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

 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

Tags (4)
1 Solution

Accepted Solutions
MVP

## Re: Consolidating Months into Quarters

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

6 Replies
MVP & Luminary

## Re: Consolidating Months into Quarters

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
MVP

## Re: Consolidating Months into Quarters

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

Contributor

## Re: Consolidating Months into Quarters

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

Contributor

## Re: Consolidating Months into Quarters

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

MVP

## Re: Consolidating Months into Quarters

Very awesome MarcoWedel‌‌

Contributor

## Re: Consolidating Months into Quarters

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