topic Re: How to create a pivot table with columns in both ends. in QlikView

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

gmu — Sun, 27 Apr 2014 08:22:03 GMT

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.

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

stevedark — Sun, 27 Apr 2014 08:43:44 GMT

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

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

gmu — Sun, 27 Apr 2014 08:53:56 GMT

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.

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

stevedark — Sun, 27 Apr 2014 10:00:12 GMT

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

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

gmu — Sun, 27 Apr 2014 10:03:02 GMT

That's my concern.

I will try your approach and check with the customer.

Thanks again.

Makis.

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

stevedark — Sun, 27 Apr 2014 10:05:47 GMT

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