Hide Columns in Pivot Table

[#1]

Hello everyone,

I'm using QV11 and I have created a pivot table that populates that table with a 13month rolling average.

The calculations in that table are correct, and I can select Year, Month and Quarters and show only those values. However, my issue is that the table also shows null cells. The expression I am currently using is below.

If(Year,rangeavg(before(sum({$<Year=,Month=>}[sales]),0,13)),null()).

If a specifc month, year or quarter is selected, the values for the select value(s) is shown. Values not within the user selection are turned null.

As an example of the output if the user selects 'August' as a month (I did select the Null symbol to equal 'Null' in the presentation tab of the pivot table):

Group		MAY 2011	JUN 2011	JUL 2011	AUG 2011
Total		NULL	NULL	NULL	76
Group X		NULL	NULL	NULL	81

Also, an example of no user selection:

Group		MAY 2011	JUN 2011	JUL 2011	AUG 2011
Total		79	76	76	76
Group X		88	85	82	81

Group X is one of a number of groups that comprise the total. As stated, values calculated are correct and fulfill requirements.

I've tried suppressing null values within the dimension tab for year/month, this did not seem to work. I also attempted to use a conditional but my knowledge of those conditionals are slim and I couldn't think of an example with any effect outside of disabling the expression.

Is there any possibility of hiding cells that are empty/null using a calculated expression?

Thanks and Regards,

Christian

[joshabbott]

Why is the values in May/Jun/Jul showing as null when in your set expression, you are eliminating the month selection?  Are you wanting there to be null's there?

[#3]

That was something I was wondering myself.

Being new to Qlikview, I started experimenting with different expressions to try and hide values. I find that without the set expression, the values are then incorrect.

I think, and excuse any error in my logic/wording, that because the set allows all values to first be added and then turned null, it results in the correct value. Without the set, it does show only the user selection, but the values are wrong- perhaps because it's trying to take the rolling average with only three available columns as opposed to all columns?

An example of removing the set is seen below :

Group		AUG 2011	AUG 2012	AUG 2013
Total		78	78	79
Group X		78	78	79

[joshabbott]

Is there any chance you could upload your document?  If not, that is ok, but how do you want it to work?  Do you want it to hide or do you want it to show the values for these when August is selected?  If you want it to show the values, based on your set analysis, I would think it should work.  What is the name of the dimension they are selecting August on?  What is the name of the dimension in your pivot table for month?

[#5]

Unfortunately I am unable to share the document. I do apologize for that inconvenience.

Preferably, when the user selects a month, I would like the table to show the month(s) selected and hide the months that were not selected (same applying for year, et cetera).

The dimension name in the pivot table is CalYear/Month. The user selection names are Month and Year.

Also, apologies for a slow reply. I do not have access to my computer during the weekends.

[#6]

Just to provide a little more information:

If I remove set analysis, then only the user selection is displayed. However, since the formula uses the previous 13 months, if the user selects a year, that pivot table on sees the 12 months selected by the user. Would you happen to know why that would be?