Hide row(s) in a pivot table.

[#1]

1.How can I hide rows in a pivot table. I have Year as one of my dimension and I would like to hid the data for year = 2012?

2. Also, is there a way to have a list box that excludes(hides) few of the selection when a sheet is activated?

Thank you in advance.

[Anonymous]

1. Consider using calculated dmension:
aggr(only({<Year-={'2012'}>} Year),Year)

2. Not sure what you mean here.  If it is "hide some values", you can use calculated list box with expression:
aggr(only({<Field-={'Value1','Value2','Value3',}>} Field), Field)
Where Value1 (2,3) are values to exclude.  Better yet to create new Field2 in the script that is the same as old Field but without some values.
You do not need to do anythng about "active sheet" if this list box exists only on one sheet.

Regards,
Michael

[#3]

I tried the function you sent me for 1. but it's giving me an aggregated data.  What I would like to do is show years 2009,2010 and 2011 and remove 2012 from the pivot tbl. I would like the other years to show individually and not aggregated.

Also, on number 2 I really don't follow your answer, is there a way you can give me a sample? thank you for your help!

[Anonymous · Accepted solution]

See attached

[robert99]

1

Im unsure why you need to use aggr

Why can't you just use something like this in your expression formula

{<Year=Year-{2012}>}

Or as a one off select 2012 and then change to excluded

[Anonymous]

RJ,

You don't need aggr() in expression, but you need it in calculated dimension.  Besides, it it necessary to check "supress when value is null".

Regards,

Michael

[robert99]

michael

Ok Im fairly new to qlikview. I might try this formula in a dimension some time

And I should have said include this {<Year=Year-{2012}>}  in all expression formula's

[#8]

Thanks this was perfect!!