Hide a column in Pivot Table

[Anonymous]

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.

[JonnyPoole]

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]

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]

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.

[#5]

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]

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]

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]

were you able to achieve desired  result ? Please share solution