How to create a pivot table with columns in both ends.

[gmu]

Hi gurus.

I am faced with the following "reporting" requirement:

I want to create a report like the attached one, where I will have a pivot table with columns in both ends of the table.

The purpose of this being, to have English description on the left hand side, Arabic description on the right hand side and figures in between.

Has anybody created something like this?

Thank you in advance.

Regards,

Makis.

[stevedark]

Pivot tables in QlikView are very fixed items.  You always have dimensions on the left and/or top and values beneath and to the right.

You could potentially get to what you are after by using a straight table.  What you would need to do for this though is have different expressions for each of the years (rather than a single expression and Year as a dimension).  To get each column to only show one year you would need Set Analysis - something like this:

=sum({<Year*={'2014'}>}Value)

Or you could chose to have a calculated value of max(Year) and then max(Year)-1 in the expression.

You could then either leave it so that you always show all years or in the Presentation tab you can suppress columns on a conditional basis (say to hide old years when no data in the selection).  The calculation for each column would be something like:

=sum({<Year*={'2014'}>}Value) <> 0

There are quite a few cases where in order to get a desired result you need to use a Straight Table rather than a pivot, this feels to me to be one of them.

Hope that helps.

Steve

[gmu]

Thank you Steve.

Your approach will bring the right results at the end of the day.

The problem is that I will not be able to reproduce the desired presentation, having for instance the gender and the year on the top of the table, with the corresponding grouping.

Thank you once again.

Makis.

[stevedark · Accepted solution]

You will need to create the Male/Female columns as separate expressions also.  Again using Set Analysis:

=sum({<Year*={'2014'},Gender*={'Male'}>}Value)

When choosing to display the columns or not you would want to ignore the gender - as I suspect you would either want to show both male and female or neither.

The downside of this approach is that you couldn't have the year label spanning two columns, you would have to have 2014 Male and 2014 Female as separate legends.

Steve

[gmu]

That's my concern.

I will try your approach and check with the customer.

Thanks again.

Makis.

[stevedark]

The only way you can get around that with straight tables is by having the year legends in a separate table that hovers above with transparency on - but that is fiddly and likely to go wrong.  Hope the client is amenable to the duplication of the year legend - sell it on the fact that when you export to Excel it is cleaner that each column has a full legend!

Steve