hide column when it's all 0

[yelin_nyu]

I have a straight table look like below

product    Global qty      International qty     NA qty     EMEA qty     APAC qty

A               1000               700                         300          0                    700

Region is not in dimension becaue i need to show global and internation qty on the same page as regional breakdown. i used set analysis/if statement in each expression to specify the region (ie. region=EMEA, region<>NA,etc)

suppress zero in properties would not work in this case, tried that already.

I have a condition to show each region when it's selected.  substringcount(concat(region, '|'), 'NA'), but I want to auto hide columns (EMEA) where the expression is 0 or null when i don't make any selection.

[yelin_nyu]

another thing i want to add, i am looking for something simple. I know i can potentially do if( substringcount(concat(region, '|'), 'NA')>0 or expression>0,1,0)

but the actual expressions are really really longggggg, plus the actual table has 50 expressions, i have take performance into consideration. and my expression name is also dynamic, depending on few conditions it will change accordingly

[orital81]

Hi Lin Ye, please attach your example, it will be easier to help you this way.

[yelin_nyu]

product	sum($<{region=>},qty))	sum(if(region<>'NA',qty))	sum(if(region='NA',qty))	sum(if(region='EMEA',qty))	sum(if(region='APAC',qty))
A	1000	700	300	0	700
B	2000	1000	1500	0	500

as you can see emea didn't any qty for product A & B, so i want to hide any column that the expression=0

rememer this table is a simplified of what i really deal with. i can write in condition show/hide if(column(n)>0,1,0)., but when i have 72 expressions in total, this gets really cumbersome and i lose track which column im on. and this will slow down the tool as well. im wondering if there is a quick self referencing expression, like currentcolumn(0 or currentexpression.

[Anonymous]

Hi Lin,

have you tried suppressing missing or zero values (under table properties-->presentation)?

hope that helped

[yelin_nyu]

yes, notice that my expressions are using if and set analysis, region is not in dimension. so suppress 0 will not work.

[jagan]

Hi,

If you are using Qlikview 11, then use Conditional expression so that you can hide the column

Or else use Hide Column option in Presentation tab of Straight Table.

Hope this helps you.

Regards,

Jagan.

[orital81]

Dear Lin, I suggest to use pivot table with Product and Region as dimentions and Qty as expression.

This way you will get a column per Region dynamicaly and 0 values will not be shown (as long as you suppress zero values).

Dim1:  Region

Dim 2: Product 

Expression: [  Qty  ]