topic Re: Consolidating Months into Quarters in QlikView

Consolidating Months into Quarters

Karl_Hart — Tue, 13 Oct 2015 08:23:13 GMT

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

Re: Consolidating Months into Quarters

Gysbert_Wassenaar — Thu, 22 Oct 2015 16:40:58 GMT

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.

Re: Consolidating Months into Quarters

MarcoWedel — Thu, 22 Oct 2015 22:07:42 GMT

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

Re: Consolidating Months into Quarters

Karl_Hart — Fri, 23 Oct 2015 06:50:06 GMT

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

Re: Consolidating Months into Quarters

Karl_Hart — Fri, 23 Oct 2015 06:51:24 GMT

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

Re: Consolidating Months into Quarters

sunny_talwar — Fri, 23 Oct 2015 12:14:54 GMT

Very awesome MarcoWedel‌‌

Re: Consolidating Months into Quarters

Karl_Hart — Wed, 25 Nov 2015 11:49:34 GMT

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

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

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

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