topic Re: Hide a column in Pivot Table in QlikView

Hide a column in Pivot Table

Anonymous — Wed, 17 Sep 2014 02:53:36 GMT

Hi,

I've attached a screenshot of our actual report. Its a nested pivot table and there are some columns that we want to appear only on particular year.

As shown in the picture, is it possible to hide those columns from the rest of the years and appear only to a particular year?

Any trick or macro is greatly appreciated.

Thanks.

Re: Hide a column in Pivot Table

JonnyPoole — Wed, 17 Sep 2014 03:23:03 GMT

You could try going from 4 expressions to 1 and adding a new dimension for the expression names . Call it [ExpressionName] . That way you can associate some of the names to certain years and not others in the data model. You won't get expression names associating to certain years and appearing as columns in the same way you wouldn't get certain countries associating to certain continents... just need to put year in a lookup table with the expression name to map the way you want.

You would need to do 4 resident loads in the script to pivot the data into 1 expression called [ExpressionValue]. So intead of 4 expression fields you have 2 fields in lieu called [ExpressionName] and [ExpressionValue]. The table will have more records and is less wide.

In the chart you would use a nested IF to calculate the expression value depending on the expression name.

use num() functions to format different expression differently in the chart expression.

Re: Hide a column in Pivot Table

Anonymous — Mon, 26 Jan 2015 09:02:32 GMT

hi there,,

im still looking for a solution to this..

I need to hide the higlighted column from year 2011-12, 2012-2013 and show it only on year 2013-14.

a macro or any display trick would be greatly appreaciated...

Many thanks!

Re: Hide a column in Pivot Table

jagan — Mon, 26 Jan 2015 09:48:12 GMT

Hi,

Instead of creating 4 expressions add one more dimension as synthetic dimension like

=ValueList('Exp1', 'Exp2', 'Exp3', 'Exp4')

Now in expression try like this

=If(ValueList('Exp1', 'Exp2', 'Exp3', 'Exp4') = 'Exp1', Some expression 1,

If(ValueList('Exp1', 'Exp2', 'Exp3', 'Exp4') = 'Exp2', Some expression 2,

If(ValueList('Exp1', 'Exp2', 'Exp3', 'Exp4') = 'Exp3' and Year = '2013-14' , Some expression 3,

If(ValueList('Exp1', 'Exp2', 'Exp3', 'Exp4') = 'Exp4' and Year = '2013-14' , Some expression 4))))

Hope this helps you.

Regards,

Jagan.

Re: Hide a column in Pivot Table

Mon, 26 Jan 2015 09:54:18 GMT

If the single column is linked to a single expression, make the expression conditional based on a simple if statement - pseudo code if(year(datevalue)<year(today()),1,0)

otherwise is there any reason you cant use set analysis in the expression to exclude values you are not interested in?

Re: Hide a column in Pivot Table

Anonymous — Mon, 26 Jan 2015 10:03:30 GMT

Hi,

In Expression Tab-->Select the expression --> tick conditional-->Give your condition to hide column for year you don't want to display data

Regards

Neetha

Re: Hide a column in Pivot Table

Peter_Cammaert — Mon, 26 Jan 2015 10:41:36 GMT

Columns with expressions cannot be suppressed. Columns for dimension values can.

An easy approach (with an INLINE table), but now with a practical example matching your case: show / hide column with condition in pivot table

Once you master this, proceed to implement Jagan's suggestion. The solution with ValueList() is more streamlined and easier to maintain.

Re: Hide a column in Pivot Table

Qlik2019 — Fri, 05 Jul 2019 20:06:11 GMT

were you able to achieve desired result ? Please share solution

Re: Hide a column in Pivot Table

Alexis314 — Wed, 09 Oct 2024 08:12:17 GMT

Hello, can you give more detailed explanations to fully understand your explanation?
Thank you very much.