How do you suppress a dimension in a pivot table when all values in the current selection are null

[justin_morley]

Hi,

Firstly, apologies, as I'm sure this has been asked a million times, but no amount of rephrasing my question to google has turned up the answer I need.

I have a pivot table, with many, many dimensions. Most of these have conditional show enabled. I'd like to extend these conditional formulae to say "With the current selection applied, if all values for the given dimension are null, don't display this column".

NB This is *not* the same as the check box "Suppress When Value Is Null" which suppresses displaying the whole row. I want that row to still be displayed, just the column to disappear.

Thanks in advance,

Justin

[rajeshvaswani77]

Justin,

Dimensions would always refer to the whole row.

Straight table in presentation tab has hide column conditional.

That will serve your purpose.

thanks,

Rajesh Vaswani

[swuehl · Accepted solution]

A count(DimensionField) should equal zero than, right (counting only NULLs should result in zero)?

So maybe add this to your dimension conditional:

... and count(DimensionField)

[#4]

Could someone please point me to some documentation that describes the function of the "Conditional" box on both the Dimension and the Expression tabs? I can't find anything in the manual that describes how these work, and what their purpose is.

Thanks,

Kevin

[justin_morley]

Thanks swuehl,

This works like a charm

[justin_morley]

For bonus points could anyone answer the following probably much harder question?

I already have the contents of my conditional formula in a variable, so I can update multiple conditionals in one go.

Using swuehl's correct answer above I now need to very laboriously add a clause to each conditional:

$(vGridConditional(param)) & (count (ColumnA)>1)

Is there any way to say generically "for the current column", so I can make a change in just a single place, rather than the 200 odd edits I'm now faced with ?

e.g.

<the contents of vGridConditional> and (count("MyCurrentColumn")>1)

[justin_morley]

Hi Kevin,

A little digging has uncovered this link:

http://livingqlikview.blogspot.co.uk/2012/01/conditional-dimensions-and-expressions.html

Is this a sufficient explanation for you?

I'm using conditional dimensions in the example here to hide a column when all values in it are null.

When you check "Enable Conditional" the box is expecting a statement which returns a Boolean (i.e. true or false - these evaluate to -1 or 0 in QlikView terms)

So in my example I include the statement:

count (myDimension) >0

If all values for myDimension are null this statement evaluates to 0 and the dimension doesn't display. However if there is a value somewhere in my table in this dimension, it evaluates to -1 and the dimension is displayed. This is an incredibly useful and powerful feature for me.

Justin

[#8]

Thanks, justin. That helps a lot in understanding. I have a huge table with three basic areas: sales activity (# of calls, last call date, etc.), sales forecast (forecast and sale amounts and times), and misc (did they have a survey? where did we find them? etc.)

The table is so big, it takes a few minutes to refresh, and some columns don't appear where one expects them to. If we could activate/de-activate these areas based on user input, it might make the table more useable.I'll have to test it out, but thanks again for showing me how this works!

[justin_morley]

Hi Kevin,
Glad to be of help.
The real power of this is it has enabled me to categorise my columns and toggle whole categories on and off in one click. This makes many dimensions much easier to handle.
I do this by firstly creating a little data island in my script containing my categories:

TOGGLE_COLUMNS:
LOAD * Inline [
ToggleColumn
CategoryA
CategoryB
];

I display a list of checkboxes for each of these categories by using a List Box control with field set to ToggleColumn and Selection Style Override (in the Presentation tab) set to Windows Checkboxes.

I then set up the following conditional in my dimensions for a given category:
index(GetFieldSelections(ToggleColumn,',',8),CategoryA)>0

which effectively says "concatenate all selected categories in ToggleColumn, then return true if CategoryA is present, i.e. it is currently selected"

I then concatenate the boolean clause discussed in this thread around nulls in the column, so it will only display a dimension if its category is selected and the dimension has something other than just nulls to display.

Actually to make it neater I've place the whole lot into a variable vGridConditional and I pass the category name in, but that's another story.

Justin

[#10]

Thanks a lot! Also worked for me.

Display second dimensions given first dimension selected:

I wanted to display a second dimension in the same chart ONLY if the first dimension has been selected. So I ticked "Conditional" and added the following line

count(GetCurrentSelections())

Thanks for the input!

Best,

Michael