Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Hide a column in Pivot Table

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.

7 Replies
JonnyPoole
Former Employee
Former Employee

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.

Anonymous
Not applicable
Author

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!

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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?

Anonymous
Not applicable
Author

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

Qlik2019
Partner - Contributor II
Partner - Contributor II

were you able to achieve desired  result ? Please share solution