topic Headers over Column Names in QlikView

Headers over Column Names

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have to create a table where I am using expressions to generate the columns "Apr '16", "May '16".....

How do I create headers on top of these columns as shown below?

I need to create Headers "Quantity", "Monthly" and "Yearly" on top of the month names.

Note: These header values do not exist anywhere, so I can't use them as Dimension and pull on top of the Month Columns.

Re: Headers over Column Names

sunny_talwar — Thu, 28 Apr 2016 18:22:29 GMT

Where are you imagine those headers will go in your attachment? I am having difficulty imagining where are you wanting these headers to do?

Re: Headers over Column Names

rubenmarin — Thu, 28 Apr 2016 18:34:06 GMT

Hi, you can try adding a calculated dimension like:

=ValueList('Quantity', 'Monthly', 'Yearly')

In this case the expressions should check the top header:

If(ValueList('Quantity', 'Monthly', 'Yearly')='Quantity', QuantityExpression,

If(ValueList('Quantity', 'Monthly', 'Yearly')='Monthly', QuantityExpression,

If(ValueList('Quantity', 'Monthly', 'Yearly')='Yearly', QuantityExpression)))

Re: Headers over Column Names

Anonymous — Thu, 28 Apr 2016 18:48:27 GMT

I need to categorize the monthly columns into "Quantity", "Monthly" and "Yearly", Sunny. That's how I am doing in the Excel manually. But how do I create those Category Headers in Qlikview is my question.

Ruben Marin gave something to try. Am gonna try it and see if it works.

Re: Headers over Column Names

sunny_talwar — Thu, 28 Apr 2016 18:50:20 GMT

Yup, give it a short. If it doesn't work, I suggest sharing a sample of what you are envision and we might be able to help you better

Re: Headers over Column Names

rubenmarin — Thu, 28 Apr 2016 19:01:12 GMT

If doesn't works I support the sample idea, it's easier for us and you will get a tested answer instead of a try... althought the option to test yourself is good for self-learning.

Btw: Another easy option if columns are fixed is using text boxes to simulate the headers.

Re: Headers over Column Names

Anonymous — Thu, 28 Apr 2016 19:02:00 GMT

For your better understanding of the problem statement, I have created a sample file.

It contains Month and Value as Columns.

I am creating expressions so that I get the total sum for each month.

I need to create another header over months as shown below:

Jan 2016, Feb 2016, Mar 2016 columns should have another main header over their columns names saying "2016 Quarter1".

Similarly, Apr 2016, May 2016 and May 2016 should have another header over the month names saying "2016 Quarter2".

How do I get that header?

Re: Headers over Column Names

sunny_talwar — Thu, 28 Apr 2016 19:06:40 GMT

Like this?

I used a pivot table.

Dimensions

1st Dim - Month

2nd Dim - =Year(Date#(Month, 'MMM YYYY')) & ' Quarter' & Ceil(Month(Date#(Month, 'MMM YYYY'))/3)

3rd Dim - Month

Note: Had to use Date#() function because your Month field was not read as date. You probably won't need to do it

Expression

Sum(Value)

Re: Headers over Column Names

Anonymous — Thu, 28 Apr 2016 20:04:28 GMT

Thanks Sunny. Here is the catch.

With this case, adding new Dimensions and Expressions is messing up the whole table.

When I add new Expression, it is populating under every month, as the dimension does that work. This definitely works if I only have Month and the Value in the Chart but once I start adding other dims and exps, it messes up

Re: Headers over Column Names

sunny_talwar — Thu, 28 Apr 2016 20:06:07 GMT

Can you may be share an updated qvw where the things are getting messed up

Re: Headers over Column Names

Anonymous — Thu, 28 Apr 2016 20:20:19 GMT

Hi Sunny,

I just created another expression called OCTOBER 2016. You will find that the expression is being evaluated over every month. This expression is just an example. If this is the case, if I add other dimensions and expressions that I need to include in the chart, it will sabotage whole chart

Re: Headers over Column Names

sunny_talwar — Thu, 28 Apr 2016 20:23:42 GMT

What is the sabotage?

Just had to move some stuff around, but isn't this what you want?

Re: Headers over Column Names

Anonymous — Thu, 28 Apr 2016 20:25:40 GMT

Do you see the OCTOBER 2016 expression being evaluated for every month? Why do we need that? If that is happening to this expression, if I add more expressions further, they all will be evaluated for every month (in this case 12 times for 12 months).

Re: Headers over Column Names

sunny_talwar — Thu, 28 Apr 2016 20:28:52 GMT

Yes, that seems to be an issue. I haven't able to resolve this. May be someone else can

Re: Headers over Column Names

Anonymous — Thu, 28 Apr 2016 20:30:29 GMT

Yeah, but your solution does work like a charm if I am dealing only with two entities, Month and the values corresponding to those months. Unfortunately, I need to add many more dimensions and expressions.

Re: Headers over Column Names

sunny_talwar — Thu, 28 Apr 2016 20:55:43 GMT

See if this helps:

Script:

Load * Inline

[

Month,Value

Jan 2016,2

Jan 2016,4

Jan 2016,6

Feb 2016,1

Feb 2016,3

Mar 2016,5

Mar 2016,6

Apr 2016,10

Apr 2016,1

May 2016,4

May 2016,3

Jun 2016,1

Jun 2016,5

Jul 2016,2

Jul 2016,6

Aug 2016,1

Sep 2016,7

Oct 2016,1

Oct 2016,4

Oct 2016,7

Nov 2016,9

Dec 2016,10

];

Dim:

LOAD * Inline [

ExpHeader

];

Dimensions

1) Month

2) =Year(Date#(Month, 'MMM YYYY')) & ' Quarter' & Ceil(Month(Date#(Month, 'MMM YYYY'))/3)

3) Month

4) =Pick(ExpHeader, 'Sum Total', 'Sum Oct')

Expression

=Pick(ExpHeader, Sum(Value), if(Month='Oct 2016', sum(Value)))

As long as you have just one expression, everything can work for you.

Re: Headers over Column Names

Anonymous — Thu, 28 Apr 2016 21:01:11 GMT

Thanks Sunny. We can do this for one expression. But we can't keep adjusting for other dimensions and expressions if they are too many. I will think about what you have provided.

Re: Headers over Column Names

sunny_talwar — Thu, 28 Apr 2016 21:02:50 GMT

You can do this for 100 expression in a go with a very long pick statement