topic Re: Merge headers in pivot table in QlikView

Merge headers in pivot table

Tue, 11 Aug 2009 18:36:32 GMT

Hi Qlikheads!

Pls. refer to the enclosed qlikview file for reference.

I have the following object:

In order to make the table easier to read I would like to add a "merged header" to the columns regarding the time interval. Consequently the monthly and YTD columns will be grouped accordingly:

Normally I would add an extra dimension and then drag it to the top using the "pivoting" option. This will not work for me here though since I dont want the same set of calculation for both month and Ytd. E.g. my variance should only apply for Ytd. This is the reason why I am using set analysis in my expressions and therefore actually have an expression for each column.

How can I achieve the merged header and make it so flexible that I might add more columns (e.g. FY) and them group them into a 3rd header.

Looking much forward to your replies.

Lars Madsen

Merge headers in pivot table

Tue, 11 Aug 2009 21:11:36 GMT

I have 'resolved' this in the past by putting text boxes above the table to give the same appearance and using color coding for the columns. Other than that I am not aware or a solution (I am using 8.20)

Regards,

Gordon

Merge headers in pivot table

Wed, 12 Aug 2009 13:26:14 GMT

Thanks Gordon

Your proposal might work ok in some situtaions.

I don't particularly like to group together various QV objects though since the risk of inconsistancies increases. In this example my main concern is, that my end users are using QV for various reporting purposes and therefore are used to export the objects to e.g. office applications either as actual values or as image.

Using the standard QV functionality will only export the table and not the headers which will be a major issue. I have earlier on proposed to QlikTech that making it possible to group together objects would solve this issue (in addition this would also be very handy together with the auto minimize functionality which could then be used seperately on multible groups of objects)

In addition to the above I see a problem though when having multible vertical dimensions in a pivottable. Expanding a high level dimension will move the expressions columns to the right. Consequently the header labels (in text boxes) will no longer be aligned.

Thanks again Gordon - pls. accept that I will leave the thread open.

Lars

Merge headers in pivot table

johnw — Thu, 13 Aug 2009 01:50:28 GMT

This didn't work, but perhaps it is part of a solution.

I added valuelist('Month','YTD') as a calculated dimension. For the expressions, I then checked the valuelist for the appropriate value, like this:

sum({$<MONTH = {">=$(=Min(MONTH))<=$(=Max(MONTH))"}, SCENARIO={"ACTUAL"}>} if(valuelist('Month','YTD')='Month',VALUE))

Unfortunately, I then couldn't figure out how to suppress the column in the other heading, so it still showed up with all 0. Maybe there's a way to suppress it, though.

Merge headers in pivot table

Thu, 13 Aug 2009 13:51:18 GMT

Hi John!

Thanks for the effort. I actually tried the exact same thing whith the same conclusion .

I did some more searching through the forum I didn't exactly find what I need so I took the liberty to open a new thread that is more focused on the specific issue:

"Multible column widths for expr. X based on dim. Y": http://community.qlik.com/forums/t/19525.aspx

Lars

Merge headers in pivot table

Thu, 13 Aug 2009 17:49:58 GMT

Hi Lars,

Please find the file attached with, In this file you will find the same data and columns merging tech.

Hope this helps.

Regards, Amit

Merge headers in pivot table

johnw — Fri, 14 Aug 2009 02:22:35 GMT

Ah, of course! The best solutions seem obvious in hindsight.

If I'm following your suggestion correctly, Amit, here it is applied to Lars' example.

Merge headers in pivot table

Fri, 14 Aug 2009 18:17:04 GMT

Amit and Jonh!

OMG - you did magic - it works so great. And with a very simple and flexible setup.

I just applied it to my live application and it works .

Thanx - I really appreciate it!

Lars

Re: Merge headers in pivot table

Mon, 04 Nov 2013 07:42:01 GMT

Im looking for the answer provided here. But cant find it anywhere, and the links are broken. Can anyone explain how the column header solution was accomplished?

Thank you.

Re: Merge headers in pivot table

Thu, 04 Sep 2014 11:35:55 GMT

Hi All,

thanks for the solutions.

I Also need to insert the current selected year instead of 'Month' Header

And the Last Year of the selected Year as the second Header ('YTD').

Is it possible?

Than it will be a dynamic sub header

Thanks

Re: Merge headers in pivot table

Anonymous — Wed, 06 Apr 2016 19:42:52 GMT

Hi Amit and John,

I see the Group column names implemented in the QVW but couldn't figure out how you did it. If either of you could provide the steps for achieving it, it would be really appreciated.

Re: Merge headers in pivot table

Mon, 17 Apr 2017 14:45:10 GMT

Excellent contribution!!!

Re: Merge headers in pivot table

gireesh1216 — Mon, 10 Jul 2017 13:52:26 GMT

Hi Lars,

Could you please let me know, what properties did you change/set in the pivot table to get this feature of merged header.

Thanks,

Gireesh