Merge headers in pivot table

[#1]

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

[#2]

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

[#3]

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

[johnw]

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.

[#5]

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

[#6 · Accepted solution]

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

[johnw]

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.

[#8]

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

[#9]

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.

[#10]

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