topic Re: a cross-table issue in QlikView

a cross-table issue

Anonymous — Sun, 19 Jul 2015 11:06:02 GMT

Hi,

I'd like to colour the rows in my cross-table with white and grey stripes.

In order to do so I apply the following formula for the background colour: if(mod(RowNo(),2)=0,RGB(242,242,242))

But this method of colouring doesn't work for those cells where the value is missing. See the file attached.

Is there another way to colour a cross table with white and grey stripes?

Thank you in advance,

Larisa

Re: a cross-table issue

arthur_dom — Sun, 19 Jul 2015 11:24:02 GMT

Try deselecting "Supress Zero Values"

Re: a cross-table issue

Anonymous — Sun, 19 Jul 2015 11:34:01 GMT

Hi,

'Supress zero-values' has been already deselected. It doesn't solve the problem.

There are two types of null vaues: nulls and missings. Nulls are displayed as nulls in my cross-table and missings are displayed as '-'. That's correct. Supress zero-values is unchecked.

Re: a cross-table issue

arthur_dom — Sun, 19 Jul 2015 11:35:36 GMT

Try:

if( isnull( <Expression>), 0, <Expression>)

So you replace missing with 0

Re: a cross-table issue

marcus_sommer — Sun, 19 Jul 2015 11:42:06 GMT

You could try to apply your background-color (additionally) to the custom cell format. Another approach could be to try to set the cell-value to 0 maybe with: alt(YourExpression, 0).

- Marcus

Re: a cross-table issue

Anonymous — Sun, 19 Jul 2015 11:58:52 GMT

I like the idea of using the custom cell format. But can't understand why it fail to work. As you can see in the attached file, there's a field expression in the custom cell format area. If I type the expression and then qlik 'apply', QV automatically clears my expression. Is it a bug?

As for another idea: I need to show the symbol '-' for the end user, so I can't convert '-' to 0.

Re: a cross-table issue

marcus_sommer — Sun, 19 Jul 2015 12:07:57 GMT

I could imagine that it isn't possible to use a calculated expression within the custom cell format and qv had forgotten to disable these option which is now confusing

Maybe dual() could be used to show '-':

dual(replace(alt(YourExpression, 0), 0, '-'), alt(YourExpression, 0))

- Marcus

Re: a cross-table issue

Anonymous — Sun, 19 Jul 2015 12:32:34 GMT

Thanks, Marcus!

Is it possible to correct your formula taking into account that not all NULLs shoud be converted to '-'?

Actually, in QV there 2 types of missing values.

1) blank strings

2) and real nulls (the result of a calculated expression)

I tried your formula. It converts both types of missings into '-'. But I don't need to convert the second type of missings to '-'.

Is it possible to correct your formula so that real nulls (the second type) could not be converted to '-'?

Re: a cross-table issue

marcus_sommer — Sun, 19 Jul 2015 12:41:26 GMT

Maybe so:

dual(if(isnull(YourExpression), 'NULL',

if(len(YourExpression) = 0, 'MISSING',

YourExpression)),

alt(YourExpression, 0))

- Marcus

Re: a cross-table issue

Anonymous — Sun, 19 Jul 2015 14:48:20 GMT

Thank you, Marcus!

But it seems that while using the isnull-function inside the if-function it is not possible to set the if-false-part of the if-function.